## Match 2 Columns, Return 3rd, Differing Match Types

### Match 2 Columns, Return 3rd, Differing Match Types

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!!

### Match 2 Columns, Return 3rd, Differing Match Types

Hi,

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

change range to fit your needs

### Match 2 Columns, Return 3rd, Differing Match Types

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

### Match 2 Columns, Return 3rd, Differing Match Types

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.