Example of identifier on 2 columns

To explain the techniques, let's start with this example where you have for each same date, a different for each currency.

Here you MUST build your ID with these 2 columns because

• For the same date, you have different quotations for each currency
• The currency code is repeated each day

So, the only way to return the correct value, it's to build an ID with the combination of the column Date and Currency.

VLOOKUP isn't the function to use 😮

VLOOKUP has been designed (in 1983) to search on the first column of your range of data.

But there is a trick, with VLOOKUP to be able to search on more than one column. You must transform your table references.

1. Add a blank column on the first position of your table
2. Combine (concatenate) the 2 columns to build your unique ID
3. Repro the steps for the second table

This solution is really not good because 

• it will take time to build
• you can make mistakes
• and the most basic Excel rule is to NEVER MODIFY YOUR SOURCE OF DATA.

But XLOOKUP will do the job 😉

The XLOOKUP function is one of the new Excel functions accessible only with Microsoft 365, Excel Online and Excel 2021.

This function has greatly improved the shortcomings of the VLOOKUP function, such as the possibility of building a search on 2 columns 😉

Construction of the XLOOKUP formula to search on 2 columns

Step 1: Build the first value to search with 2 cells

We will first build our identifier by merging the cells E2 and F2 cells with the symbol &

=XLOOKUP(E2&F2;

Step 2: Build your lookup table

Now we must build the "lookup table". That means we need to do the same with the table reference.

=XLOOKUP(E2&F2,$A$2:$A$13&$B$2:$B$13

Only, here, it is necessary to respect the order of construction of the key and the search table

• The order of the key is Date + Employee Code
• The construction of the table must also respect the same order. Hence the association of columns C and A (in this order)."

The construction of the reference table in memory is only possible with the interpretation of dynamic matrix functions. This is why this technique is only possible with Excel 365.

Step 3: Add the column to return.

All that remains is to indicate the column to be returned and the function is complete 😀👍

=XLOOKUP(E2&F2,$A$2:$A$13&$B$2:$B$13)

And the result is perfect 😍😍😍😍😍