Find the nearest number using INDEX and MATCH but with more than one condition.

[ed809]

Example I have 2 sheets in a work book "ListA" with a table and "Main"

Sheet "ListA"

A B C
--------------
red 3 10
blue 2 11
red 4 12
red 1 8
blue 3 7

In my sheet "Main" I will like to find the nearest value in that table from "ListA" column "C", but also that the color is only blue.

A B
Color blue
Value 10

I was doing: =INDEX(ListA!B1:B5,MATCH(MIN(ABS(ListA!C1:C5-Main!B2)),ABS(ListA!C1:C5-Main!B2),0)) but I didnt find how to include the color "Row A" on my conditions the value on Main!B1

Thanks in advance

 

[Marcelo Branco]

Hi and welcome to Mr Excel

Maybe this array formula

=INDEX(ListA!$B$1:$B$5,MATCH(MIN(IF(ListA!$A$1:$A$5=B1,ABS(ListA!C1:C5-B2))),IF(ListA!$A$1:$A$5=B1,ABS(ListA!C1:C5-B2)),0))

Ctrl+Shift+Enter

Hope this helps

M.

 

[Aladin Akyurek]

Color	blue
Value	10
2

<COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY>
</TBODY>

main, A4, control+shift+enter, not just enter:

=INDEX(ListA!$B$2:$B$6,
  MATCH(MIN(ABS(IF(ListA!$A$2:$A$6=B1,ListA!$C$2:$C$6,9.99999999999999E+307)-B2)),
  ABS(IF(ListA!$A$2:$A$6=B1,ListA!$C$2:$C$6-B2)),0))