How do you search in 2 columns in Excel?
- This task is not easy when you do it with VLOOKUP 😟
- But if you use XLOOKUP, you can solve this problem straightforward 😃👍
Why Search Two Columns?
When working with dates and currencies, each date has different values for each currency. The currency code repeats daily.
- Each date can have multiple currency values.
- The same currency code appears more than once.
To get accurate results, we must build a unique identifier by combining the Date and Currency columns.
- This document has been created with the STOCKHISTORY function
VLOOKUP Isn’t Ideal
VLOOKUP only searches in the first column 😡. This is how the function has been developed. Now, if you need to search in 2 columns, you must transform the table and concatenate the two columns to create a new column.
- Add a blank column in the first column to your table.
- Concatenate Date and Currency to create an ID with the & sign
- Repeat for the lookup table.
After that, you create your VLOOKUP function by using the column as the first column of your reference table.
XLOOKUP Is Better
If you work with Excel 365, Excel Online, and Excel 2021, it’s better to use the XLOOKUP function. This function has been developed to remove the limitations of VLOOKUP. This includes performing a search in two columns 😉👍
- Combine the values in E2 and F2 using the & sign to create your identifier
E2&F2 - Concatenate the columns in your table reference to match your ID.
$A$2:$A$13&$B$2:$B$13 - Enter the column you want to return
$C$2:$C$13, and your formula is ready!
This way, the XLOOKUP function returns a single result based on merging 2 cells and a search in 2 columns.
