VLOOKUP with Multiple Values or Criteria Using INDEX and MATCH (How To)

Multiple VLOOKUP LeadVLOOKUP is a great tool for pulling data from tables, but it has a handicap: it can only work with one criteria for matching information. If there are multiple rows in your sheet with the same information, you’ll only get the first one. If you need to use two or more conditions to match a specific piece of data, you’re out of luck. Fortunately, Excel has a pair of functions called INDEX and MATCH that can help produce the same results as VLOOKUP with multiple criteria. Here’s a quick tutorial to help you learn how…

VLOOKUP Example Data

Let’s say, for example, that we want to be able to search through a list of fuel economy data for cars to find the mileage…

Car Data

Normally, we would want to be able to enter the model of a car and get it’s fuel economy as a result. Unfortunately, Many cars, like the Acura ILX, have multiple engine configurations with different mileage ratings. Fortunately, in this case, the car’s displacement can serve to separate them.

Displacement Highlight

This means, however, that we will need to look up the car by both its Model and its Displacement at the same time to find the appropriate Combined Fuel Economy in column H.