Perform VLOOKUP with Multiple Lookup Values in Three Ways

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

Posted in Blog and tagged , , , , .