How to do a VLOOKUP on 2 columns

How to do a VLOOKUP on 2 columns
Last Updated on 20/09/2023
Reading time: 3 minutes

There is 2 ways to perform research on 2 columns with VLOOKUP or XLOOKUP

  1. The function VLOOKUP

    With this technique, you must concatenate the 2 columns to create your key column

  2. The function XLOOKUP

    You can build your lookup column with more than one column

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.

Search based on 2 columns

VLOOKUP isn't the function to use 😮

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

VLOOKUP searches only in the first column

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
Add ID column builds by concatenating 2 columns

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;

The first argument of XLOOKUP is build with 2 columns

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

The second argument is your virtual reference table

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)

The last argument is the column to return

And the result is perfect 😍😍😍😍😍

Formula to return the value based on 2 columns

Leave a Reply

Your email address will not be published. Required fields are marked *

How to do a VLOOKUP on 2 columns

Reading time: 3 minutes
Last Updated on 20/09/2023

There is 2 ways to perform research on 2 columns with VLOOKUP or XLOOKUP

  1. The function VLOOKUP

    With this technique, you must concatenate the 2 columns to create your key column

  2. The function XLOOKUP

    You can build your lookup column with more than one column

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.

Search based on 2 columns

VLOOKUP isn't the function to use 😮

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

VLOOKUP searches only in the first column

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
Add ID column builds by concatenating 2 columns

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;

The first argument of XLOOKUP is build with 2 columns

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

The second argument is your virtual reference table

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)

The last argument is the column to return

And the result is perfect 😍😍😍😍😍

Formula to return the value based on 2 columns

Leave a Reply

Your email address will not be published. Required fields are marked *