VLOOKUP 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…
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…
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.
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.
Andrew Roberts has been solving business problems with Microsoft Excel for over a decade. Excel Tactics is dedicated to helping you master it.
Jump start your analysis with a one-on-one consultation or join the newsletter to stay on top of the latest articles. Sign up and you'll get a free guide with 10 time-saving keyboard shortcuts!
Other posts in this series...
- How to VLOOKUP with Multiple Criteria Using INDEX and MATCH
- Faster Multiple Criteria Lookups with VLOOKUP and CONCATENATE
- Extract a List of Values Filtered by Criteria with Sub-Arrays
- How to Combine (Concatenate) Data from Multiple Rows into One Cell
- How to Unpivot Columns into Rows in Excel Using Power Query