Index Match (multiple columns)

[ScottSom]

I am using Excel 2007 (hopefully upgrading soon) and I am trying to look up some values in a large spreadsheet using the Index Match and i am running into problems with the last match. The first 3 columns i am looking for an exact match by word or number but the final i am looking for something greater than or equal to the value i have entered. How should the formula read when one is not an exact match?

Appreciate any help, thanks
Scott

 

[Aladin Akyurek]

Try to be more specific. You could perhaps post a small sample.

 

[ScottSom]

Here is a small sample of the data and the search info i would be trying to find. Length is great than or equal to the value entered.

Table
Dept	Size	Code	Length	Rate
1	0.5	RD	1.082	1000
1	0.5	SQ	2.082	2000
1	0.75	RD	2.999	3000
1	0.75	RD	3.416	4000
2	0.5	RD	1.082	1000
2	0.5	REC	2.082	2000
2	0.75	REC	2.999	3000
2	0.75	RD	3.416	4000
Find			>=
Dept	Size	Code	Length	Rate
1	0.75	RD	3.01	?

<colgroup><col style="width: 28pt; mso-width-source: userset; mso-width-alt: 1353;" width="37"> <col style="width: 32pt; mso-width-source: userset; mso-width-alt: 1572;" width="43"> <col style="width: 38pt; mso-width-source: userset; mso-width-alt: 1865;" width="51"> <col style="width: 44pt; mso-width-source: userset; mso-width-alt: 2121;" width="58"> <col style="width: 42pt; mso-width-source: userset; mso-width-alt: 2048;" width="56"> <tbody>
</tbody>

 

[Aladin Akyurek]

Thanks for the sample. Not sure what value you want to see in the stead of the question mark...

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

=IFERROR(1/(1/MIN(IF($A$2:$A$9=$A13,IF($B$2:$B$9=$B13,
    IF($C$2:$C$9=$C13,IF($D$2:$D$9>=$D13,$E$2:$E$9)))))),"Not available")

 

[ScottSom]

Thanks for the Help. I wasn't thinking of using the nested If statment in an array, I did manage to get it to work using Max instead of Min. One question, i am trying the figure out what the "1/(1/" is for?

Thanks agian

 

[Aladin Akyurek]

Thanks for the Help. I wasn't thinking of using the nested If statment in an array, I did manage to get it to work using Max instead of Min. One question, i am trying the figure out what the "1/(1/" is for?

Thanks agian

Are you saying that you needed the highest rate associated with the stated conditions?

 

[ScottSom]

Yes and the formula works using the max instead of min that you had posted. Thank you, i just can't quite follow the logic in your statement with the 1/(1/.

 

[Aladin Akyurek]

Yes and the formula works using the max instead of min that you had posted. Thank you,

You are welcome.

i just can't quite follow the logic in your statement with the 1/(1/.

When no data is available, that bit will cause an error on which IFERROR acts with "not available." If a max value is available, say 50,

1/(1/50)

will return 50 as we want.

 

[ScottSom]

I fully understand now, because it provides an error because it's a logic statement. Thanks so much for the Help!

 

[Aladin Akyurek]

I fully understand now, because it provides an error because it's a logic statement. Thanks so much for the Help!

Glad to help.