Welcome to Excel Jet Consult. In this tutorial, we will learn how to perform VLOOKUP with multiple lookup values in three ways. Let’s get started
In the caption below, we have a sample data to work with
Note, we have list data validation in cells F2:H2. So, we want to return the Sales Total result for Abiola David with States and Product that equals Lagos and Beverages respectively
Create Helper Column
We want to create an helper column where we can combine all the values in columns A:C. To do that
- Create a new column in column A and provide heading
- In cell A2, enter the formula: =B2&C2&D2
- Click Enter and copy down the formula
VLOOKUP & CONCAT Method
In cell J2, we want to return the Total using the combination of VLOOKUP and CONCAT functions. Note, you can use the CONCATENATE function in the place of CONCAT if you currently do not have Office 365 and Microsoft Office 2019 versions
- In cell J2, execute the formula: =VLOOKUP(CONCAT(G2,H2,I2),A2:E22,5,FALSE)
- Click Enter and copy down the formula
VLOOKUP with Ampersand Method
We want to replicate the same result using the ampersand sign (&)
- In cell J2, execute the formula: =VLOOKUP(G2&H2&I2,A2:E22,5,FALSE)
- Click Enter and copy down the formula
VLOOKUP with TEXTJOIN Method
- In cell J2, execute the formula: =VLOOKUP(TEXTJOIN(,TRUE,G2,H2,I2),A2:E22,5,FALSE)
- Click Enter and copy down the formula. Note, TEXTJOIN, unlike CONCAT function, is only available to Office 365 and Microsoft Office 2019 versions.
We achieved the same result using the three methods