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.

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:

• First, read our introduction to INDEX MATCH. This article will give you important background on the basics of the formula and why it is better than VLOOKUP in many scenarios.
• Then, read our article on looking up against multiple criteria using INDEX MATCH. This is a more advanced tutorial that will show you how to solve the above problem quickly and easily.

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.

Comments