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
[TABLE="width: 536"]
<tbody>[TR]
[TD="class: xl67, width: 156"]sample_id[/TD]
[TD="class: xl68, width: 70"]HOLE_ID[/TD]
[TD="class: xl68, width: 77"]FROM(m)[/TD]
[TD="class: xl68, width: 76"]TO(m)[/TD]
[TD="class: xl69, width: 157"]ROCK_CODE[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 536"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]C505054[/TD]
[TD]WS06-143[/TD]
[TD]78.18[/TD]
[TD]79.86[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD]C505055[/TD]
[TD]WS06-143[/TD]
[TD]79.86[/TD]
[TD]81.69[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
</tbody>[/TABLE]

Sheet2
[TABLE="width: 260"]
<tbody>[TR]
[TD="class: xl67, width: 70"]HOLE_ID[/TD]
[TD="class: xl67, width: 62"]FROM(m)[/TD]
[TD="class: xl67, width: 47"]TO(m)[/TD]
[TD="class: xl67, width: 81"]ROCK_CODE[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 260"]
<tbody>[TR]
[TD="class: xl67, width: 70"]WS06-143[/TD]
[TD="class: xl68, width: 62"]78.18[/TD]
[TD="class: xl68, width: 47"]81.69[/TD]
[TD="class: xl67, width: 81"]GABR[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Sheet1
[TABLE="width: 536"]
<tbody>[TR]
[TD="class: xl67, width: 156"]sample_id
[/TD]
[TD="class: xl68, width: 70"]HOLE_ID
[/TD]
[TD="class: xl68, width: 77"]FROM(m)
[/TD]
[TD="class: xl68, width: 76"]TO(m)
[/TD]
[TD="class: xl69, width: 157"]ROCK_CODE
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 536"]
<tbody>[TR]
[TD]C505054
[/TD]
[TD]WS06-143
[/TD]
[TD]78.18
[/TD]
[TD]79.86
[/TD]
[TD="align: center"]#N/A
[/TD]
[/TR]
[TR]
[TD]C505055
[/TD]
[TD]WS06-143
[/TD]
[TD]79.86
[/TD]
[TD]81.69
[/TD]
[TD="align: center"]#N/A
[/TD]
[/TR]
</tbody>[/TABLE]

Sheet2
[TABLE="width: 260"]
<tbody>[TR]
[TD="class: xl67, width: 70"]HOLE_ID
[/TD]
[TD="class: xl67, width: 62"]FROM(m)
[/TD]
[TD="class: xl67, width: 47"]TO(m)
[/TD]
[TD="class: xl67, width: 81"]ROCK_CODE
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 260"]
<tbody>[TR]
[TD="class: xl67, width: 70"]WS06-143
[/TD]
[TD="class: xl68, width: 62"]78.18
[/TD]
[TD="class: xl68, width: 47"]81.69
[/TD]
[TD="class: xl67, width: 81"]GABR
[/TD]
[/TR]
</tbody>[/TABLE]

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:

[TABLE="class: grid, width: 150, align: left"]
<tbody>[TR]
[TD]Month
[/TD]
[TD]Skill
[/TD]
[TD]Calls Offered
[/TD]
[TD]Abandoned Calls
[/TD]
[TD]% Of Abandoned
[/TD]
[/TR]
[TR]
[TD]Sept
[/TD]
[TD]400
[/TD]
[TD]100
[/TD]
[TD]10
[/TD]
[TD]10
[/TD]
[/TR]
[TR]
[TD]Sept
[/TD]
[TD]401
[/TD]
[TD]200
[/TD]
[TD]20
[/TD]
[TD]10
[/TD]
[/TR]
[TR]
[TD]Oct
[/TD]
[TD]400
[/TD]
[TD]300
[/TD]
[TD]30
[/TD]
[TD]10
[/TD]
[/TR]
[TR]
[TD]Oct
[/TD]
[TD]401
[/TD]
[TD]200[/TD]
[TD]10
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Oct
[/TD]
[TD]402
[/TD]
[TD]100
[/TD]
[TD]5
[/TD]
[TD]5
[/TD]
[/TR]
</tbody>[/TABLE]











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:

[TABLE="class: grid, width: 150, align: left"]
<TBODY>[TR]
[TD]Month
[/TD]
[TD]Skill
[/TD]
[TD]Calls Offered
[/TD]
[TD]Abandoned Calls
[/TD]
[TD]% Of Abandoned
[/TD]
[/TR]
[TR]
[TD]Sept
[/TD]
[TD]400
[/TD]
[TD]100
[/TD]
[TD]10
[/TD]
[TD]10
[/TD]
[/TR]
[TR]
[TD]Sept
[/TD]
[TD]401
[/TD]
[TD]200
[/TD]
[TD]20
[/TD]
[TD]10
[/TD]
[/TR]
[TR]
[TD]Oct
[/TD]
[TD]400
[/TD]
[TD]300
[/TD]
[TD]30
[/TD]
[TD]10
[/TD]
[/TR]
[TR]
[TD]Oct
[/TD]
[TD]401
[/TD]
[TD]200
[/TD]
[TD]10
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]Oct
[/TD]
[TD]402
[/TD]
[TD]100
[/TD]
[TD]5
[/TD]
[TD]5
[/TD]
[/TR]
</TBODY>[/TABLE]











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.