This article will introduce you to 2 techniques to perform a search based on the content of 2 columns.
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 a same date, you have different quotation for each currency
- The currency code is repeated for 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.
- Add a blank column on the first position of your table
- Combine (concatenate) the 2 columns to build your unique ID
- 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's 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 &
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.
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 😀👍
And the result is perfect 😍😍😍😍😍