VLOOKUP - Multiple Criteria

G

gregula82

New Member
Joined
Aug 18, 2006
Messages
8
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.
 
Sort by date Sort by votes
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)))
 
Upvote 0

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
BiocideJ said:
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)))
Click to expand...

Worked perfectly! Thank you for the fast reply!
 
Upvote 0
Seanm290 said:
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.
Click to expand...

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

Either...
Rich (BB code): 
=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...
Rich (BB code): 
=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.
 
Upvote 0
T. Valko said:
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

*ABCD

<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
Click to expand...

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!
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
Matching multiple criteria that includes comma separated value
Replies
2
Views
197
Rokcmd
R
A
  • Solved
Index Match With Multiple Criteria
Replies
3
Views
318
acool
A
A
  • Question Question
Excel - looking up data with multiple criteria from table/array, date must be same or nearest before the target cell
Replies
8
Views
628
Peter_SSs
Peter_SSs
C
  • Question Question
How to show a list view of columns based off another sheet / alternative to Pivot table?
Replies
7
Views
129
Domenic
D
J
  • Solved
Filter function with multiple and|or criteria not working
Replies
3
Views
590
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,853
Messages
6,181,412
Members
453,038
Latest member
muhsen

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top