Using VLOOKUP with multiple values

The VLOOKUP function is one of the most useful capabilities within Microsoft Excel. It's an important tool within any seasoned pro's arsenal, and is one of the first advanced functions that most beginners learn.

But sometimes, a simple lookup is not enough. There are times during which you may need to reference a result based on multiple values rather than a single criteria. Is it possible to do this using VLOOKUP?

Defining the problem

Take a look at the spreadsheet below, which lists the number of cookies and brownies sold by SnackWorld by month.

Number of cookies and brownies sold by month

We want to know whether there is an easy way to look up the number of items sold for any given month-snack combination — for example, the number of cookies sold in February.

The solution

The good news is, there is a solution to this problem. The bad news is, it doesn't have to do with VLOOKUP:

The VLOOKUP function is designed to only look up data off of one column. The formula requires a column count argument, and the function's search is restricted to the single column that you specify in that position. In order to perform a lookup with multiple criteria, then, you'll need to look to another important Excel tool: INDEX MATCH.

INDEX MATCH is not actually a single function — it is a combination of Excel's INDEX and MATCH functions. Separately, these features are useful; but used together, they are a more powerful version of the lookup functions we know and love.

To get started looking up on multiple criteria with INDEX MATCH:

Once you've read the above articles, you'll have a thorough understanding of how to perform a lookup based on multiple values in Excel.

Save an hour of work a day with these 5 advanced Excel tricks

Work smarter, not harder. Sign up for our 5-day mini-course to receive must-learn lessons on getting Excel to do your work for you.

  • How to create beautiful table formatting instantly...
  • Why to rethink the way you do VLOOKUPs...
  • Plus, we'll reveal why you shouldn't use PivotTables and what to use instead...

By submitting this information, you agree to Deskbright's privacy policy and terms of service.

Comments