**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:**

## One Response

Using VLOOKUP:

=VLOOKUP(I3&I4&I5&I6,CHOOSE({1,2},B3:B14&C3:C14&D3:D14&E3:E14,F3:F14),2,0)