Developer Forum

Hello,

I have 3 columns of data in my main table. For example:

Blue / 2 / $5
Blue / 4 / $10
Blue / 6 / $15
Green / 3 / $4
Green / 10 / $8
Red / 1 / $1
Red / 4 / $3

What I am looking to do is use some kind if Index/Match function (I
think) to look up the color (column A) and number (column B) and have
the price returned (column C). I have found lots of solutions for
this out there, however the one part I keep getting stuck on is when I
need to look up something like the following...

Blue / 5 = #N/A. I want the answer to be $10 but since the number 5
is not an exact match with 2, 4 or 6 an error is returned.

How can I set up a formula where it is using a match type of 1,
instead of the exact match on the number lookup portion (column b).

Maybe I need to take a totally different route with the whole thing?
Thank you in advance for any suggestions you are able to provide!!

Hi,

=INDEX(C1:C4,MATCH(E1,A1:A4,0)*MATCH(F1,B1:B4,1))

change range to fit your needs

One way...

*IF* the data is grouped by color then sorted in ascending order by column
B.

Assuming the data is in the range A2:C8

E2 = lookup color
F2 = lookup number

=LOOKUP(F2,OFFSET(B2:C2,MATCH(E2,A2:A8,0)-1,,COUNTIF(A2:A8,E2)))

--
Biff
Microsoft Excel MVP

Just in case the data isn't always grouped and sorted:

=INDEX($C$2:$C$8,
MATCH(MAX(INDEX(($A$2:$A$8=E2)*($B$2:$B$8<=F2)*$B$2:$B$8,)),
INDEX(($A$2:$A$8=E2)*$B$2:$B$8,),0))

HTH
Steve D.