VLOOKUP is definitely a great tool to get the data from tables, however it has limitations: it can only work with one criteria for matching information. What If you need to refer two or more conditions to match a specific piece of data, VLOOKUP will only get the first match in the data. Fortunately Excel has functions called INDEX and MATCH that works same as VLOOKUP but can do wonders with multiple criteria lookup. Here’s a quick tutorial to help you learn how…

Download Example File at the end of article..

For example, we want to get the price of specific gadget from the list of gadgets stores data…

Normally, when we enter the model we would want to get the price of a gadget as a result. Its is impossible for naked VLOOKUP to get the price of a desired model as there are many companies, many gadgets, for instance Dell 7″ Tablet has a price change in years. Fortunately, in this case, the combination of product criteria can serve to separate them.

This means, we will need to lookup the gadget make year, company, product and size at the same time to find the appropriate price.

Also read: Using INDEX and MATCH to Replace VLOOKUP

MATCH is a function that gives you the location of an item in an array. The syntax for MATCH is as follows:

=MATCH(lookup_value, lookup_array, [match_type])

The lookup_value is what you are searching for.
The lookup_array is the array of values you are trying to find the lookup_value in.
The optional match_type determines whether MATCH must find the lookup_value exactly (with a 0), or return the closest match that comes before it (with a 1) or after it (with a -1) alphanumerically.

The INDEX function takes a location and returns the value that is in the cell. The syntax for INDEX is as follows:

=INDEX(array, row_num, [col_num])

The array is the table of data that contains the cell value you want.
The row_num is the relative row number of the cell you want.
The col_num is the relative column number of the cell you want.

By combining INDEX and MATCH we can produce the same result as VLOOKUP. Using the same search we did for VLOOKUP

Using INDEX and MATCH with Two Criteria

To allow MATCH to search for multiple criteria, we are going to change the way it looks for its result by making it an array formula.An array formula takes an array of values instead of a single one and checks each cell in the array until it finds a result.

{=MATCH(LookupValue1&LookupValue2,LookupArrary1&LookupArray2,MatchType)}

Also read: How to Build an ARRAY Formula in Excel

Instead of lookup for Single Value in Single column, we are combining multiple values and multiple ranges to get the exact location.

Note: Do not forget the sequence should be in sync for both LookupValues & LookupArrays. I mean, if we are searching for LookupValue1 and LookupValue2 the range should be in order line LookupArrary1 and LookupArray2.

If this process doesn’t make sense to you, that’s okay. Just plug the new MATCH function into your INDEX/MATCH formula:

{=INDEX(F3:F14,MATCH(I3&I4&I5&I6,B3:B14&C3:C14&D3:D14&E3:E14,0))}

When you enter the formula, don’t just press ENTER. Press CTRL+SHIFT+ENTER to tell Excel that it is an array formula. You can tell you’ve done it right because the entered formula will be surrounded in curly braces {}

With that, your formula will be able to find the gadget price based on both the given criteria. You can use this technique for any number of criteria with INDEX and MATCH. Just add additional terms to the LookupValue and LookupArray.

Download Sample File: VLOOKUP with Multiple Criteria

Also Read:

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

12 − 4 =

This site uses Akismet to reduce spam. Learn how your comment data is processed.