Index Match (multiple columns)

S

ScottSom

New Member
Joined
Mar 2, 2015
Messages
6
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
 
Sort by date Sort by votes
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="width: 184"]
<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>[TR]
[TD="width: 80, bgcolor: transparent, colspan: 2"]Table[/TD]
[TD="width: 51, bgcolor: transparent"][/TD]
[TD="width: 58, bgcolor: transparent"][/TD]
[TD="width: 56, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: #4F81BD"]Dept[/TD]
[TD="class: xl66, bgcolor: #4F81BD"]Size[/TD]
[TD="class: xl66, bgcolor: #4F81BD"]Code[/TD]
[TD="class: xl66, bgcolor: #4F81BD"]Length[/TD]
[TD="class: xl66, bgcolor: #4F81BD"]Rate[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: #B8CCE4, align: right"]1[/TD]
[TD="class: xl67, bgcolor: #B8CCE4, align: right"]0.5[/TD]
[TD="class: xl67, bgcolor: #B8CCE4"]RD[/TD]
[TD="class: xl67, bgcolor: #B8CCE4, align: right"]1.082[/TD]
[TD="class: xl67, bgcolor: #B8CCE4, align: right"]1000[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: #B8CCE4, align: right"]1[/TD]
[TD="class: xl68, bgcolor: #DBE5F1, align: right"]0.5[/TD]
[TD="class: xl68, bgcolor: #DBE5F1"]SQ[/TD]
[TD="class: xl68, bgcolor: #DBE5F1, align: right"]2.082[/TD]
[TD="class: xl68, bgcolor: #DBE5F1, align: right"]2000[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: #B8CCE4, align: right"]1[/TD]
[TD="class: xl67, bgcolor: #B8CCE4, align: right"]0.75[/TD]
[TD="class: xl67, bgcolor: #B8CCE4"]RD[/TD]
[TD="class: xl67, bgcolor: #B8CCE4, align: right"]2.999[/TD]
[TD="class: xl67, bgcolor: #B8CCE4, align: right"]3000[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: #B8CCE4, align: right"]1[/TD]
[TD="class: xl68, bgcolor: #DBE5F1, align: right"]0.75[/TD]
[TD="class: xl68, bgcolor: #DBE5F1"]RD[/TD]
[TD="class: xl68, bgcolor: #DBE5F1, align: right"]3.416[/TD]
[TD="class: xl68, bgcolor: #DBE5F1, align: right"]4000[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: #B8CCE4, align: right"]2[/TD]
[TD="class: xl67, bgcolor: #B8CCE4, align: right"]0.5[/TD]
[TD="class: xl67, bgcolor: #B8CCE4"]RD[/TD]
[TD="class: xl67, bgcolor: #B8CCE4, align: right"]1.082[/TD]
[TD="class: xl67, bgcolor: #B8CCE4, align: right"]1000[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: #DBE5F1, align: right"]2[/TD]
[TD="class: xl68, bgcolor: #DBE5F1, align: right"]0.5[/TD]
[TD="class: xl68, bgcolor: #DBE5F1"]REC[/TD]
[TD="class: xl68, bgcolor: #DBE5F1, align: right"]2.082[/TD]
[TD="class: xl67, bgcolor: #B8CCE4, align: right"]2000[/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: #B8CCE4, align: right"]2[/TD]
[TD="class: xl67, bgcolor: #B8CCE4, align: right"]0.75[/TD]
[TD="class: xl67, bgcolor: #B8CCE4"]REC[/TD]
[TD="class: xl67, bgcolor: #B8CCE4, align: right"]2.999[/TD]
[TD="class: xl67, bgcolor: #B8CCE4, align: right"]3000[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: #DBE5F1, align: right"]2[/TD]
[TD="class: xl68, bgcolor: #DBE5F1, align: right"]0.75[/TD]
[TD="class: xl68, bgcolor: #DBE5F1"]RD[/TD]
[TD="class: xl68, bgcolor: #DBE5F1, align: right"]3.416[/TD]
[TD="class: xl67, bgcolor: #B8CCE4, align: right"]4000[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Find[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl71, bgcolor: transparent"]>=[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: #4F81BD"]Dept[/TD]
[TD="class: xl66, bgcolor: #4F81BD"]Size[/TD]
[TD="class: xl66, bgcolor: #4F81BD"]Code[/TD]
[TD="class: xl66, bgcolor: #4F81BD"]Length[/TD]
[TD="class: xl66, bgcolor: #4F81BD"]Rate[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent, align: right"]1[/TD]
[TD="class: xl73, bgcolor: transparent, align: right"]0.75[/TD]
[TD="class: xl73, bgcolor: transparent"]RD[/TD]
[TD="class: xl74, bgcolor: transparent, align: right"]3.01[/TD]
[TD="class: xl75, bgcolor: transparent"]?[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
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:
Rich (BB code): 

=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")
 
Upvote 0
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
 
Upvote 0
ScottSom said:
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
Click to expand...

Are you saying that you needed the highest rate associated with the stated conditions?
 
Upvote 0
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/.
 
Upvote 0
ScottSom said:
Yes and the formula works using the max instead of min that you had posted. Thank you,
Click to expand...

You are welcome.

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

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.
 
Upvote 0
I fully understand now, because it provides an error because it's a logic statement. Thanks so much for the Help!
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
INDEX-MATCH-MATCH help!
2
Replies
15
Views
821
henrik2h
H
C
  • Question Question
XLOOKUP: Multiple criteria but one criteria is a value and may not be an exact match
Replies
1
Views
153
StephenCrump
S
C
  • Question Question
Transpose vertical CSV data set into a linked horizontal table with index match and/or lookup and multiple criteria
Replies
1
Views
99
Fluff
Fluff
B
  • Locked
  • Question Question
Help with Index & Match
Replies
1
Views
201
Fluff
Fluff
W
  • Question Question
Index/Match Duplicate Values With No Helper Cells
Replies
3
Views
563
Domenic
D

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