Back to Blog

Excel VLOOKUP For Two Values

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

How to VLOOKUP for two values

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 a car value for a user with the name “Mella“, and the Excel VLOOKUP function can do the job easily. The issue is that we have multiple users with the name “Mella“.

So, we need to narrow down our lookup using two criteria:

  • Name – “Mella
  • Country – “Nigeria

You can’t specify two lookup values 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 two lookup criteria – name and country – for example “MellaThailand“, “MellaNigeria“, etc. 
  • Step2: Look up the range using the unique strings as the lookup_value 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:
=C1:C100&E1:E1000

This formula will merge two values from columns C and E in the specified array.

Step2: VLOOKUP formula for two values

Now we can insert a VLOOKUP formula, which will contain two lookup_values merged into one:

=VLOOKUP((I4&I5),A2:G101,7,FALSE)
  • (I4&I5)– a lookup value based on the formula, which merges values from cells I4 (Mella) and I5 (Nigeria).
  • A2:G101 – the range to look up
  • 7 – the column to return the matching values from

You can apply the same logic for using more than two criteria as a lookup value.

Back to Blog

Comments are closed.

Access your data
in a simple format for free!

Start Free