How to Lookup Value with Multiple Criteria in Excel

It's easy to look up for value with one unique key in a table. We can simply use the VLOOKUP function. But when you don’t have that unique column in your data and need to lookup in multiple columns to match a value, VLOOKUP doesn’t help.

So, to lookup a value in a table with multiple criteria we will use INDEX-MATCH-INDEX formula.
1
Generic Formula for Multiple Criteria Lookup

INDEX(lookup_range,MATCH(1, INDEX((criteria1 =range1)*(criteria2=range2)*(criteriaN=rangeN),0,1),0))

lookup_range: Its the range from which you want to retrieve value.

Criteria1, Criteria2, Criteria N: These are the criteria you want to match in range1, range2 and Range N. You can have up to 270 criteria - range pairs.

Range1, range2, rangeN : These are the ranges in which you will match your respective criteria.

How it will work? Let's see…

INDEX and MATCH with Multiple Criteria Example

Here I have a data table. I want to pull the name of the customer using Date of Booking, Builder and Area. So here I have three criteria and one lookup range.
2
Write this formula in cell I4 hit enter.

=INDEX(E2:E16,MATCH(1, INDEX((I1=A2:A16)*(I2=B2:B16)*(I3=C2:C16),0,1),0))

3
How it works:
We already know how INDEX and MATCH function work in EXCEL, so i am not going to explain that here. We will talk about the trick we used here.

(I1=A2:A16)*(I2=B2:B16)*(I3=C2:C16): The main part is this. Each part of this statement return an array of true false.
When boolean values are multiplied they return array of 0 and 1. Multiplication works as AND operator. Hense when all value are true only then it returns 1 else 0
(I1=A2:A16)*(I2=B2:B16)*(I3=C2:C16) This altogether will return

{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}*
{FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE}*
{FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

Which will translate into

{0;0;0;0;0;0;0;1;0;0;0;0;0;0;0}

INDEX((I1=A2:A16)*(I2=B2:B16)*(I3=C2:C16),0,1): INDEX Function will return the same array ({0;0;0;0;0;0;0;1;0;0;0;0;0;0;0}) to MATCH function as lookup array.

MATCH(1,INDEX((I1=A2:A16)*(I2=B2:B16)*(I3=C2:C16),0,1): MATCH function will look for 1 in array {0;0;0;0;0;0;0;1;0;0;0;0;0;0;0}. And will return index number of first 1 found in array. Which is 8 here.

INDEX(E2:E16,MATCH(1,INDEX((I1=A2:A16)*(I2=B2:B16)*(I3=C2:C16),0,1),0)): Finally, INDEX will return value from given range (E2:E16) at found index (8).

Simple????. Sorry, couldn’t make it more simpler.

Array Solution

If you can hit CTRL + SHIFT + ENTER consequently then you can eliminate the inner INDEX function. Just write this formula and hit CTRL + SHIFT + ENTER.

=INDEX(E2:E16,MATCH(1,(I1=A2:A16)*(I2=B2:B16)*(I3=C2:C16),0))

Generic Array Formula for Multiple Criteria Lookup

=INDEX(lookup_range,MATCH(1,(criteria1 =range1)*(criteria2=range2)*(criteriaN=rangeN),0))

Formula works same as above explanation.

I tried my best to explain it as simple as possible. But if I was not clear enough, let me know it in the comment section below. By the way, you don’t need to know how the engine works to drive a car. You just need to know how to drive it. And you know it very well.

Related Articles:

How to Lookup Top 5 Values with Duplicate Values Using INDEX-MATCH in Excel

How to VLOOKUP Multiple Values in Excel

How to VLOOKUP with Dynamic Col Index in Excel

How to use VLOOKUP from Two or More Lookup Tables in Excel

Popular Articles:

50 Excel Shortcuts to Increase Your Productivity

How to use the VLOOKUP Function in Excel

How to use the COUNTIF function in Excel

How to use the SUMIF Function in Excel

Leave a Reply

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

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Youtube