Back to Blog

Excel VLOOKUP For Multiple Criteria

The VLOOKUP function looks up only one value. And what if you want to return a result that matches multiple criteria? Check out the following workaround.

How to VLOOKUP for multiple criteria

We imported a dataset from Google Sheets to Excel using Coupler.io, a solution for automatic data exports from multiple apps and sources. Read more about Microsoft Excel integrations for data export on a schedule.

Our purpose is to look up the first name of a user by the following criteria:

  • Country China
  • Car Toyota
  • Color Blue

You can’t specify more than one lookup value in a VLOOKUP formula, so we’ll need to use a workaround, which consists of two steps:

  • Step1: Create a separate column where we will create unique lookup values by merging our lookup criteria – country, car, and color – “ChinaToyotaBlue“. 
  • Step2: We’ll use this lookup value in a VLOOKUP formula to return a matching value.

Step1: Create a column with unique lookup values made of two lookup criteria

  • Insert a column to the left of your dataset like this. Select the entire column, insert the following formula to the formula bar and press Ctrl+Shift+Enter for Windows (Command+Return for Mac) – This will apply an array formula in Excel:
=B1:B100&C1:C1000&D1:D100

This formula will merge values from columns B, C, and D in the specified array.

Step2: VLOOKUP formula for multiple values

Now we can use a VLOOKUP formula, which will contain multiple lookup criteria merged into one lookup value:

=VLOOKUP((J3&J4&J5),A2:G101,6,FALSE)
  • (J3&J4&J5) – a lookup value based on the formula, which merges criteria from cells J3 (China), J4 (Toyota), and J5 (Blue).
  • A2:G101 – the range to look up
  • 6 – the column to return the matching values from
Back to Blog

Comments are closed.

Access your data
in a simple format for free!

Start Free