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.

 

[keillor]

I want to see the rock code. It works for both of those rows, but for a row where the sample interval is identical to the rock code interval, it gives me #N/A.

Cheers,
Cam

 

[Aladin Akyurek]

I want to see the rock code. It works for both of those rows, but for a row where the sample interval is identical to the rock code interval, it gives me #N/A.

Cheers,
Cam

So it works for the sample above, but not for some values. Care to post the values for which you get #N/A?

 

[keillor]

Sheet1

sample_id

HOLE_ID

FROM(m)

TO(m)

ROCK_CODE

<tbody>
</tbody>

C505054	WS06-143	78.18	79.86	#N/A
C505055	WS06-143	79.86	81.69	#N/A

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

Sheet2

HOLE_ID

FROM(m)

TO(m)

ROCK_CODE

<tbody>
</tbody>

WS06-143

78.18

81.69

GABR

<tbody>
</tbody>

 

[Aladin Akyurek]

Sheet1

sample_id

HOLE_ID

FROM(m)

TO(m)

ROCK_CODE

<tbody>
</tbody>

C505054	WS06-143	78.18	79.86	#N/A
C505055	WS06-143	79.86	81.69	#N/A

<tbody>
</tbody>

Sheet2

HOLE_ID

FROM(m)

TO(m)

ROCK_CODE

<tbody>
</tbody>

WS06-143

78.18

81.69

GABR

<tbody>
</tbody>

I get GABR as result in both cases with:

D2, Sheet1...

=INDEX(Sheet2!$D$2:$D$10,MATCH(1,IF(Sheet2!$A$2:$A$10=B2,
  IF(C2>=Sheet2!$B$2:$B$10,IF(D2<=Sheet2!$D$2:$D$10,1))),0))

 

[jkloi]

Hello Greg,


=LOOKUP(2,1/((A1:A100="x")*(B1:B100="y")*(C1:C100="z")),D1:D100)

This differs from the INDEX/MATCH approach because it will give the value from column D on the last row where all 3 criteria are satisfied, rather than the first.

Hey guys,
I realize this thread is old, but could some one explain why Barry used LOOKUP(2,1/ ??
I just cannot comprehend where the 2 and the 1/ comes from.

Thanks a lot!

 

[Aladin Akyurek]

Hey guys,
I realize this thread is old, but could some one explain why Barry used LOOKUP(2,1/ ??
I just cannot comprehend where the 2 and the 1/ comes from.

Thanks a lot!

http://www.mrexcel.com/forum/excel-questions/99621-lookup-value-unsorted-data.html

 

[Kyosti]

Ok so I am reviewing this thread and I don't think what has been answered thus far helps me. If anyone can help me solve this issue I would be grateful. So I have a table like such:

Month	Skill	Calls Offered	Abandoned Calls	% Of Abandoned
Sept	400	100	10	10
Sept	401	200	20	10
Oct	400	300	30	10
Oct	401	200	10	5
Oct	402	100	5	5

<tbody>
</tbody>

What I am looking for is a way to look up the month and the skill and return the value for % of Abandoned based on the two criteria.

Can ANYONE HELP!? I'm on a bit of a time crunch!!

THank you

 

[Aladin Akyurek]

Ok so I am reviewing this thread and I don't think what has been answered thus far helps me. If anyone can help me solve this issue I would be grateful. So I have a table like such:

Month	Skill	Calls Offered	Abandoned Calls	% Of Abandoned
Sept	400	100	10	10
Sept	401	200	20	10
Oct	400	300	30	10
Oct	401	200	10	5
Oct	402	100	5	5

<TBODY>
</TBODY>

What I am looking for is a way to look up the month and the skill and return the value for % of Abandoned based on the two criteria.

Can ANYONE HELP!? I'm on a bit of a time crunch!!

THank you

Let A:E houses the data/table.

G2: Sept (a month of interest)

H2: 401 (a skill of interest)

I2, control+shift+enter, not just enter:

=IFERROR(INDEX($E$2:$E$6,MATCH(H2,IF($B$2:$B$6=I2,$A$2:$A$6),0)),"Not Found")

 

[Kyosti]

Thank you for the help. Now will this work if one of the data points is on a column and the other is in a row??

 

[Aladin Akyurek]

Thank you for the help. Now will this work if one of the data points is on a column and the other is in a row??

Do you mean something like below?

G2: Sept
G3: Oct
Etc.

H1: 400
I1: 401
Etc.