Lookup Value with Multiple Criteria

Its easy to lookup for a value with one unique key in a table. We can simply use VLOOKUP. 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 upto 270 criteria – range pairs.

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

How it will work? Lets see…

INDEX and MATCH with Multiple Criteria Example

Here I have a data table. I want to pull Name of 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. But if you didn’t understand, let me know in the comment section below. By the way, you don’t need to know how engine works to drive a car. You just need the to know how to drive it. And you know it very well.

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

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 Google PlusVisit Us On Youtube