Excel match question

[blcexcel]

Hello all. I have a challenge I can't quite solve. I attached an example. I need a formula to match two criteria. I found a lot of examples on the Internet of formulas using two criteria. But in my example there is one text criteria and the second criteria is a number that I want to match as close as possible but it often won't match exactly. I'm thinking it may need to be a sumproduct formula with a range. Or I may sort the data by Quantity and have it find the nearest match to quantity. In either case I could not find any good examples.

Thanks in advance for your help!

 

[Luke M]

With your example layout:
=INDEX(A4:A13,MATCH(SMALL(IF(B4:B13=B17,ABS(C4:C13-B18)),1),IF(B4:B13=B17,ABS(C4:C13-B18)),0))

First, formula finds the smallest absoluate different between valid Qtys and the desired amount. Then, it uses the MATCH function to find that number's position within array, and feeds it to INDEX, which returns the overall label.

Note: I used the SMALL(...,1) function rather than MIN because MIN will read a FALSE as 0 if no other values present (e.g., no Product names match), but SMALL will not.

 

[blcexcel]

Thanks Luke M! This seems to be doing the trick! I always say anything can be done in Excel but I'm still always amazed when I see it.

 

[bosco_yip]

Another option,

=INDEX(A4:A13,MATCH(MIN(IF(B4:B13=B17,(C4:C13-B18)^2)),(C4:C13-B18)^2,0))

This is an array formula, confirm entered with SHIFT+CTRL+ENTER.

Regards
Bosco

 

[bines53]

Hello friends,

Another one, without CSE

=MATCH(AGGREGATE(15,6,(ABS(C4:C13-B18)/(B4:B13=B17)),1),MMULT(ABS(C4:C13-B18),1),0)

David