How to Lookup the Latest Value from Different Query into a New Column in Excel Formula

Sometimes, you may want to find the latest value from a different query or table based on a common criterion, such as a date, a name, or a category. For example, you may have a table of sales data and another table of customer information, and you want to get the latest sales amount for each customer.

There are different ways to achieve this task in Excel, but one of the most common and powerful methods is to use the combination of INDEX and MATCH functions. The INDEX function returns a value from a specific position in a range or array, and the MATCH function returns the relative position of a value in a range or array. By using these two functions together, you can lookup a value from one table based on a matching value from another table, and return the value from a specific column or row.

Procedures

To lookup the latest value from different query into a new column in Excel formula, you need to follow these steps:

1. Identify the common criterion that you want to use to match the values from different queries or tables. For example, if you want to get the latest sales amount for each customer, the common criterion is the customer name.
2. Identify the column or row that contains the latest value that you want to return. For example, if you want to get the latest sales amount for each customer, the column that contains the sales amount is the one that you want to return.
3. Identify the range or array that contains the common criterion and the column or row that contains the latest value. For example, if you have a table of sales data in the range A1:C10, where column A contains the customer name, column B contains the sales date, and column C contains the sales amount, the range that you need is A1:C10.
4. Identify the range or array that contains the common criterion that you want to match. For example, if you have another table of customer information in the range E1:F5, where column E contains the customer name and column F contains the customer email, the range that you need is E1:E5.
5. Enter the formula that combines the INDEX and MATCH functions in the cell where you want to display the result. For example, if you want to display the latest sales amount for each customer in column G, you can enter the following formula in cell G2 and copy it down:

=INDEX(A1:C10,MATCH(E2,A1:A10,0),3)

This formula works as follows:

• The MATCH function returns the relative position of the value in cell E2 (the customer name) in the range A1:A10 (the customer names in the sales data table). For example, if the value in cell E2 is “Alice”, the MATCH function returns 2, because “Alice” is the second value in the range A1:A10.
• The INDEX function returns the value from the range A1:C10 (the sales data table) at the position specified by the MATCH function (the row number) and the number 3 (the column number). For example, if the MATCH function returns 2, the INDEX function returns the value from the range A1:C10 at row 2 and column 3, which is the sales amount for “Alice”.
• The result is the latest sales amount for the customer name in cell E2. For example, if the sales amount for “Alice” is 500, the result is 500.

Explanation

The formula that we used to lookup the latest value from different query into a new column in Excel is based on the assumption that the sales data table is sorted by the sales date in ascending order, so that the latest value for each customer is at the bottom of the table. If the sales data table is not sorted by the sales date, or if there are multiple values for the same customer on the same date, the formula may not return the correct result.

To ensure that the formula returns the correct result, we need to modify it to include another condition that checks the sales date. We can use the MAXIFS function to return the maximum sales date for each customer, and then use another MATCH function to find the position of that date in the sales data table. The modified formula is:

=INDEX(A1:C10,MATCH(1,(A1:A10=E2)*(B1:B10=MAXIFS(B1:B10,A1:A10,E2)),0),3)

This formula works as follows:

• The MAXIFS function returns the maximum sales date for the customer name in cell E2 in the range B1:B10, where the customer names are in the range A1:A10. For example, if the customer name in cell E2 is “Alice”, and the maximum sales date for “Alice” in the range B1:B10 is 1/31/2024, the MAXIFS function returns 1/31/2024.
• The expression (A1:A10=E2)*(B1:B10=MAXIFS(B1:B10,A1:A10,E2)) returns an array of 1s and 0s, where 1 indicates that the customer name and the sales date in the same row match the values in cell E2 and the MAXIFS function, and 0 indicates otherwise. For example, if the customer name in cell E2 is “Alice”, and the maximum sales date for “Alice” is 1/31/2024, the expression returns the following array:

{0;1;0;0;0;0;0;0;0;0}

• The MATCH function returns the relative position of the value 1 in the array returned by the expression, which is the row number where the customer name and the sales date match the values in cell E2 and the MAXIFS function. For example, if the value 1 is in the second position in the array, the MATCH function returns 2.
• The INDEX function returns the value from the range A1:C10 (the sales data table) at the position specified by the MATCH function (the row number) and the number 3 (the column number), which is the sales amount for the customer name and the sales date that match the values in cell E2 and the MAXIFS function. For example, if the MATCH function returns 2, and the sales amount for “Alice” on 1/31/2024 is 500, the INDEX function returns 500.
• The result is the latest sales amount for the customer name and the sales date in cell E2. For example, if the sales amount for “Alice” on 1/31/2024 is 500, the result is 500.

Note: The modified formula is an array formula, which means that you need to enter it with Ctrl+Shift+Enter, instead of just Enter. You will see curly braces {} around the formula in the formula bar if you enter it correctly.

Scenario

To illustrate how the formula works, let’s use a sample scenario with some real numbers. Suppose that we have the following sales data table in the range A1:C10:

And we have the following customer information table in the range E1:F5:

We want to display the latest sales amount for each customer in column G, using the formula that we have explained above. The result should look like this:

Other approaches

Besides using the combination of INDEX and MATCH functions, there are other possible approaches to lookup the latest value from different query into a new column in Excel. Here are some of them:

• Use the VLOOKUP function with a helper column. The VLOOKUP function can lookup a value from one table based on a matching value from another table, and return the value from a specific column. However, the VLOOKUP function can only look up a value from left to right, which means that the column containing the value you look up should always be located to the left of the column containing the return value. To overcome this limitation, you can create a helper column that concatenates the customer name and the sales date, and use it as the lookup value. For example, you can enter the following formula in cell D2 and copy it down:=A2&"-"&B2

This formula concatenates the customer name and the sales date with a hyphen in between, and creates a unique identifier for each row. For example, the value in cell D2 is “Alice-1/1/2024”.

Then, you can enter the following formula in cell G2 and copy it down:

=VLOOKUP(E2&"-"&MAXIFS(B1:B10,A1:A10,E2),A1:D10,4,0)

This formula works as follows:

• The expression E2&"-"&MAXIFS(B1:B10,A1:A10,E2) concatenates the customer name in cell E2 and the maximum sales date for that customer in the range B1:B10, where the customer names are in the range A1:A10, with a hyphen in between. For example, if the customer name in cell E2 is “Alice”, and the maximum sales date for “Alice” is 1/31/2024, the expression returns “Alice-1/31/2024”.
• The VLOOKUP function looks up the value returned by the expression in the range A1:D10, where the first column contains the helper column that we created, and returns the value from the fourth column, which is the sales amount. For example, if the expression returns “Alice-1/31/2024”, the VLOOKUP function returns the value from the fourth column in the row where the first column matches “Alice-1/31/2024”, which is 500.
• The result is the latest sales amount for the customer name in cell E2. For example, if the sales amount for “Alice” on 1/31/2024 is 500, the result is 500.

Note: The VLOOKUP function requires that the lookup range is sorted by the first column in ascending order, which means that you need to sort the sales data table by the helper column before using the formula.

• Use the XLOOKUP function with a dynamic array. The XLOOKUP function is a newer and more versatile function than the VLOOKUP function, which can lookup a value from one table based on a matching value from another table, and return the value from a specific column or row. The XLOOKUP function can also look up a value from right to left, which means that you do not need to create a helper column to use it. However, the XLOOKUP function is only available in Excel 365 and later versions, and it does not work well with dynamic arrays, which are arrays that spill over multiple cells automatically. To overcome this limitation, you can use the @ operator to force the XLOOKUP function to return a single value instead of an array. For example, you can enter the following formula in cell G2 and copy it down:

=@XLOOKUP(E2&MAXIFS(B1:B10,A1:A10,E2),A1:A10&B1:B10,C1:C10)

This formula works as follows:

• The expression E2&MAXIFS(B1:B10,A1:A10,E2) concatenates the customer name in cell E2 and the maximum sales date for that customer in the range B1:B10, where the customer names are in the range A1:A10. For example, if the customer name in cell E2 is “Alice”, and the maximum sales date for “Alice” is 1/31/2024, the expression returns “Alice1/31/2024”.
• The expression A1:A10&B1:B10 concatenates the customer names and the sales dates in the sales data table, and creates an array of unique identifiers for each row. For example, the expression returns the following array:

{“Alice1/1/2024”;“Alice1/31/2024”;“Bob1/15/2024”;“Bob1/20/2024”;“Charlie1/10/2024”;“Charlie1/25/2024”;“David1/5/2024”;“David1/30/2024”;“Eve1/12/2024”;“Eve1/28/2024”}

• The XLOOKUP function looks up the value returned by the first expression in the array returned by the second expression, and returns the corresponding value from the range C1:C10, which is the sales amount. For example, if the first expression returns “Alice1/31/2024”, the XLOOKUP function returns the value from the range C1:C10 in the position where the array matches “Alice1/31/2024”, which is 500.
• The @ operator forces the XLOOKUP function to return a single value instead of an array, which avoids the spill error that may occur when using dynamic arrays.
• The result is the latest sales amount for the customer name in cell E2. For example, if the sales amount for “Alice” on 1/31/2024 is 500, the result is 500.

Note: The XLOOKUP function does not require that the lookup array is sorted, which means that you do not need to sort the sales data table before using the formula.