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

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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
1AGZData1
2XYZData2
3GYTData3
4RRRData4
5XPYData5

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

Categories
A
B
C
D
E
1
Length
Width
Height
Weight
Category
2
1
1
1
1
1
3
2
2
2
2
2
4
3
2
3
3
3
5
4
3
5
5
4
6
4
4
3
3
5
7
4
5
4
4
6

<tbody>
</tbody>

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:

Products
A
B
C
D
E
1
Length
Width
Height
Weight
Category
2
3
4
1
5
? (solution: 6)
3
3
2
3
2
? (solution: 4)

<tbody>
</tbody>

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

sample_id​
HOLE_ID​
FROM(m)​
TO(m)​
ROCK_CODE​
C505001​
WS06-143​
3.05​
3.66​
?​
C505002​
WS06-143​
3.66​
5.18​
?​

<tbody>
</tbody>

Sheet2

HOLE_ID​
FROM(m)​
TO(m)​
ROCK_CODE​
WS06-143​
0.00​
3.05​
CASN​
WS06-143​
3.05​
5.94​
GABR​

<tbody>
</tbody>

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

excelbytes
  • Solved
COUNTIFS With Lists Of Multiple Criteria
Replies
5
Views
235
Fluff
Fluff
A
  • Solved
Sumif using Multiple Criteria
Replies
4
Views
278
acool
A
A
  • Solved
Multiple Criteria in If function Returns error
Replies
8
Views
113
etaf
etaf
T
  • Question
Multiple filters on filtered list
Replies
1
Views
227
Cubist
Cubist
E
  • Question
Sumifs, Index, formula only summing first value
Replies
1
Views
325
etaf
etaf

Forum statistics

Threads
1,215,181
Messages
6,123,508
Members
449,101
Latest member
mgro123

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