Power Query Problems

How to Import the Latest Matching Value Between Excel Tables

Huma Ashraf Ch Sep 30, 2026 868 views

Question details

The user needs to find the person who most recently checked a document on or before a specific request date across tables containing millions of rows.

How to Import the Latest Matching Value Between Excel Tables

Product: Excel
Device & OS: not provided
Scenario: Performing a multi-condition lookup to find the latest chronological match in massive datasets that exceed standard spreadsheet row limits.
Observed behavior: Standard lookup formulas are insufficient for multi-condition chronological lookups, and loading millions of rows directly into an Excel worksheet is not possible due to row limits.

Before you start

Verify the total number of rows in your source data. Standard Excel worksheets have a strict limit of 1,048,576 rows, so massive datasets must be processed in the background using Power Query or an external database.

Solution 1Recommended

Use Power Query for Advanced Filtering and Sorting

Utilize Power Query to join tables, filter dates, and extract the latest matching record without loading all data into the worksheet.

A standard VLOOKUP or INDEX/MATCH formula is not sufficient because this lookup requires evaluating two conditions (document match and date comparison) while retrieving the latest record.

By processing this in Power Query and loading only the results to the Data Model, you can bypass standard worksheet limitations.

Load tables into Power Query

Select your data range and go to Data > From Table/Range to load both the request table and the check log table into the Power Query Editor.

Merge the queries

Click 'Merge Queries' on the Home tab and join the two tables using the 'document' column as the matching identifier.

Filter the dates

Expand the merged table to reveal the check dates. Apply a Number/Date filter to ensure 'dateCheck' is less than or equal to 'requestDate'.

Sort and keep the latest record

Sort the 'dateCheck' column in descending order so the most recent dates appear at the top.

Remove duplicates

Select the 'document' column and choose 'Remove Duplicates' to keep only the first matching row, which now represents the most recent valid check.

Use Power Query for Advanced Filtering and Sorting

Data Model Loading: When closing Power Query, choose 'Close & Load To...' and select 'Only Create Connection' or 'Add this data to the Data Model' to prevent Excel from attempting to load millions of rows into a single sheet.

Solution 2

Process the Data Using a Database System

For source files containing multiple millions of rows, use a scalable database platform to perform the lookup query efficiently.

Free Microsoft Office alternative

Manage Your Everyday Data Efficiently with WPS Office

While processing millions of rows requires dedicated database architecture, WPS Office provides a lightweight, highly compatible, and lightning-fast alternative for everyday spreadsheet analysis, complex filtering, and reporting.

Free and lightweight office suite for Windows, Mac, and LinuxFully compatible with Microsoft Excel formats (.xlsx, .xlsm, .csv)Familiar user interface ensuring seamless migration with no learning curveHigh performance for processing standard data analysis and lookup formulas

microsoft office alternative - wps office

Frequently Asked Questions

Why can't I use VLOOKUP to find the latest matching value?

Standard VLOOKUP only searches for a single condition and immediately returns the first match it finds from the top down. It cannot evaluate a secondary condition, like finding a date less than or equal to a target, nor can it sort the results to ensure it grabs the most recent occurrence.

What is the maximum number of rows an Excel worksheet can hold?

An Excel worksheet is strictly limited to 1,048,576 rows. If your dataset contains millions of rows, you cannot load it directly into a worksheet without truncating data. You must use Power Query connections or an external database.

Can Power Query handle data larger than the worksheet limit?

Yes, Power Query can connect to and process files with millions of rows in the background. As long as you aggregate or filter the data down before loading, or choose to load the final results into the Data Model rather than directly onto a worksheet, it seamlessly bypasses the row limit.

Excel & Data Problems