VLOOKUP - Multiple Criteria

[gregula82]

Hi Everyone,

I was wondering whether anyone knew of a way of performing a VLOOKUP function which has multiple criteria. e.g. the lookup value would have 3 separate criteria and then you put the table array in and the column index.

Any ideas???

Thanks.

Greg.

 

[BiocideJ]

In XL2007 and later you can wrap the whole thing in an IFERROR function...
=IFERROR(INDEX(Sales!$C$4:$C$1000,MATCH(1,(Sales!$A$4:$A$1000='New P&L Detail Template (2)'!$D32)*(Sales!$B$4:$B$1000='New P&L Detail Template (2)'!$E32),0)),0)

In prior versions:
=IF(ISERROR(INDEX(Sales!$C$4:$C$1000,MATCH(1,(Sales!$A$4:$A$1000='New P&L Detail Template (2)'!$D32)*(Sales!$B$4:$B$1000='New P&L Detail Template (2)'!$E32),0))),0,INDEX(Sales!$C$4:$C$1000,MATCH(1,(Sales!$A$4:$A$1000='New P&L Detail Template (2)'!$D32)*(Sales!$B$4:$B$1000='New P&L Detail Template (2)'!$E32),0)))

 

[Seanm290]

In XL2007 and later you can wrap the whole thing in an IFERROR function...
=IFERROR(INDEX(Sales!$C$4:$C$1000,MATCH(1,(Sales!$A$4:$A$1000='New P&L Detail Template (2)'!$D32)*(Sales!$B$4:$B$1000='New P&L Detail Template (2)'!$E32),0)),0)

In prior versions:
=IF(ISERROR(INDEX(Sales!$C$4:$C$1000,MATCH(1,(Sales!$A$4:$A$1000='New P&L Detail Template (2)'!$D32)*(Sales!$B$4:$B$1000='New P&L Detail Template (2)'!$E32),0))),0,INDEX(Sales!$C$4:$C$1000,MATCH(1,(Sales!$A$4:$A$1000='New P&L Detail Template (2)'!$D32)*(Sales!$B$4:$B$1000='New P&L Detail Template (2)'!$E32),0)))

Worked perfectly! Thank you for the fast reply!

 

[Aladin Akyurek]

New to the site and need some assistance with this function. I've got it all down and working, except if the two criteria are not met, how do I make the formula return a zero instead of #N/A. Is that possible with this function?

=INDEX(Sales!$C$4:$C$1000,MATCH(1,(Sales!$A$4:$A$1000='New P&L Detail Template (2)'!$D32)*(Sales!$B$4:$B$1000='New P&L Detail Template (2)'!$E32),0))

Thanks in advance.

When such a formula is expected to return a number, on all versions, control+shift+enter, not just enter:

Either...

=LOOKUP(9.99999999999999E+307,
  CHOOSE({1,2},0,INDEX(Sales!$C$4:$C$1000,
   MATCH(1,(Sales!$A$4:$A$1000='New P&L Detail Template (2)'!$D32)*
    (Sales!$B$4:$B$1000='New P&L Detail Template (2)'!$E32),0))))

Or...

=LOOKUP(9.99999999999999E+307,
  CHOOSE({1,2},0,INDEX(Sales!$C$4:$C$1000,
   MATCH(1,IF(Sales!$A$4:$A$1000='New P&L Detail Template (2)'!$D32,
    IF(Sales!$B$4:$B$1000='New P&L Detail Template (2)'!$E32,1)),0))))

On later versions, wrap the formula expression into an IFERROR call, as already suggested.

 

[DrPitchard]

Assuming there is only one instance where:

A1:A100="x" AND B1:B100="y" AND C1:C100="z"

When all those logical tests are multiplied together the result will be an array of 0s and a 1. Then we want to find the 1 in that array and return the corresponding value from column D.

Let's see how that works with this sample data:

Sheet1

*	A	B	C	D

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:55px;"><col style="width:55px;"><col style="width:55px;"><col style="width:55px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]Z[/TD]
[TD="align: center"]Data1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]Z[/TD]
[TD="align: center"]Data2[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]T[/TD]
[TD="align: center"]Data3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]
[TD="align: center"]R[/TD]
[TD="align: center"]R[/TD]
[TD="align: center"]R[/TD]
[TD="align: center"]Data4[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]Data5[/TD]

</tbody>

This array formula**:

=INDEX(D1:D5,MATCH(1,(A1:A5="x")*(B1:B5="y")*(C1:C5="z"),0))

Result = Data2

Each of these expressions will return an array of TRUE or FALSE:

(A1:A5="x")
(B1:B5="y")
(C1:C5="z")

A1 = X = FALSE
A2 = X = TRUE
A3 = X = FALSE
A4 = X = FALSE
A5 = X = TRUE

B1 = Y = FALSE
B2 = Y = TRUE
B3 = Y = TRUE
B4 = Y = FALSE
B5 = Y = FALSE

C1 = Z = TRUE
C2 = Z = TRUE
C3 = Z = FALSE
C4 = Z = FALSE
C5 = Z = FALSE

Then these arrays are multiplied together:

FALSE * FALSE * TRUE = 0
TRUE * TRUE * TRUE = 1
FALSE * TRUE * FALSE = 0
FALSE * FALSE * FALSE = 0
TRUE * FALSE * FALSE = 0

We now have this array:

0
1
0
0
0

When we align that array with the range we want the result to come from:

0 Data1
1 Data2
0 Data3
0 Data4
0 Data5

So we look for the 1 and return the result from column D that corresponds to the 1.

=INDEX({Data1;Data2;Data3;Data4;Data5},MATCH(1,{0;1;0;0;0},0))

MATCH returns the *relative* position of the lookup value within an array. In this case MATCH = 2.

Return the value from position 2 of this array:

Position 1 = Data1
Position 2 = Data2
Position 3 = Data3
Position 4 = Data4
Position 5 = Data5

Position 2 = Data2

So:

=INDEX(D1:D5,MATCH(1,(A1:A5="x")*(B1:B5="y")*(C1:C5="z"),0))

= Data2

I am one of those guilty people out there, who use the internet to gain information and answers to my countless roadblocks and issues that I face in Excel. And this has been going on for years!! Since 2010, so that's easily a good 3 years.

Come across this explanation regarding VLOOKUP multiple criteria, and found it absolutely perfect and brilliant, that I just had to register, login and leave this feedback.

Absolutely wonderful and crystal clear explanation. Thank you so much, T.Valko!

 

[WinteE]

A simple solution to lookup on multiple criteria:

Excel Guide --- - go to Excel, Lookup, Multiple lookup

 

[WinteE]

Sorry, saved double.

 

[KyToe]

Hello everyone. I'm having a problem closely related to other problems already discussed in this thread. To keep it simple: I need to match items into groups according to their size and weight (length, witdh, height). I have created different length/width/height/weight categories, which I sorted the items in already. Now I need to match the items with their different dimensions categories into created size categories. As an example:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Categories
[/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Length
[/TD]
[TD]Width
[/TD]
[TD]Height
[/TD]
[TD]Weight
[/TD]
[TD]Category
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]3
[/TD]
[TD]2
[/TD]
[TD]3
[/TD]
[TD]3
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]4
[/TD]
[TD]3
[/TD]
[TD]5
[/TD]
[TD]5
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]4
[/TD]
[TD]4
[/TD]
[TD]3
[/TD]
[TD]3
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]4
[/TD]
[TD]5
[/TD]
[TD]4
[/TD]
[TD]4
[/TD]
[TD]6
[/TD]
[/TR]
</tbody>[/TABLE]

These categories are supposed to work as smaller or equal to, for instance 1 is <=20cm, 2 is <=50, etc. This should result in an item that is in length 1 and width 2 being shown as being a category 2 item (if it also fulfills the other requirements).

Now I have the following item:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Products
[/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Length
[/TD]
[TD]Width
[/TD]
[TD]Height
[/TD]
[TD]Weight
[/TD]
[TD]Category
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]3
[/TD]
[TD]4
[/TD]
[TD]1
[/TD]
[TD]5
[/TD]
[TD]? (solution: 6)
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]3
[/TD]
[TD]2
[/TD]
[TD]3
[/TD]
[TD]2
[/TD]
[TD]? (solution: 4)
[/TD]
[/TR]
</tbody>[/TABLE]

The formula that I created after the example in this thread is:

=INDEX(Categories!E2:E7;MATCH(1;(Products!A2<=Categories!A2:A7)*(Products!B2<=Categories!B2:B7)*(Products!C2<=Categories!C2:C7)*(Products!D2<=Categories!D2:D7);1))

This is a simplified example as compared to my situation with 16 Product Categories and 6-9 of each of the length/width/height/weight classes. The formula always gives the number of the last size category (16), no matter what numbers are given. I have tried turning the numbers categories the other way around (9=small/1=big as opposed to 9=big/1=small). The only way I have gotten it to work was with 1 and in a few cases 2 criteria. If anyone knows how to solve this I would be very grateful.

 

[KyToe]

I solved the problem. The Match Formula really only seems to work with only 2 values. I created a combined Length/Witdh Category, a combined Height/Weight Category and then matched those together to the final size categories. There were some troubles along the way but it is possible to work out. Also sorry for the mistake in the second table of my first post: the weight in D2 is supposed to be 4 or smaller to make the solution fit.

 

[keillor]

Alright, I'm having some trouble with this concept.

My file is very similar to the apple example from the first page. The key difference between my file and the apple example; however, is that I'm trying to classify short intervals based on a larger interval that they fit between. That makes no sense when I put it in words - this should help:

Sheet1

[TABLE="width: 536"]
<tbody>[TR]
[TD]

sample_id​

[/TD]
[TD]

HOLE_ID​

[/TD]
[TD]

FROM(m)​

[/TD]
[TD]

TO(m)​

[/TD]
[TD]

ROCK_CODE​

[/TD]
[/TR]
[TR]
[TD]

C505001​

[/TD]
[TD]

WS06-143​

[/TD]
[TD]

3.05​

[/TD]
[TD]

3.66​

[/TD]
[TD]

?​

[/TD]
[/TR]
[TR]
[TD]

C505002​

[/TD]
[TD]

WS06-143​

[/TD]
[TD]

3.66​

[/TD]
[TD]

5.18​

[/TD]
[TD]

?​

[/TD]
[/TR]
</tbody>[/TABLE]

Sheet2

[TABLE="width: 260"]
<tbody>[TR]
[TD]

HOLE_ID​

[/TD]
[TD]

FROM(m)​

[/TD]
[TD]

TO(m)​

[/TD]
[TD]

ROCK_CODE​

[/TD]
[/TR]
[TR]
[TD]

WS06-143​

[/TD]
[TD]

0.00​

[/TD]
[TD]

3.05​

[/TD]
[TD]

CASN​

[/TD]
[/TR]
[TR]
[TD]

WS06-143​

[/TD]
[TD]

3.05​

[/TD]
[TD]

5.94​

[/TD]
[TD]

GABR​

[/TD]
[/TR]
</tbody>[/TABLE]

I'm trying to write a formula which will search Sheet2 for a corresponding drillhole ID, and output the rock code for the (larger) interval that my sample occurs within. It works...except when my sample interval (in Sheet1) is identical to the rock code interval (in Sheet2), ie. the entire rock code interval is comprised of only one sample, as opposed to several.

This is what I have:
{=INDEX(Sheet2!D:D,MATCH(1,(hole_id=$B2)*(from<=$C2)*(to>=$D2),0))}

Please let me know what I can do to make it behave.

Thank-you very much,
Cheers,
Cam

 

[Aladin Akyurek]

What are the results you want to see?