Find closest match in a horizontal

[edtwiest]

I have items with a current value and now we have done some calculations en the new results are columns E-L

If i want to have the nearest match to item 1-002, the nearest match should be 150 [closer to 167,39 then 207]

I've tried to do index and match with multiple criteria, but all i got was errors.
Maybe some kind of matrix-formule??

I hope some wizzkid overhere is able to help me out.

Thanks in advance, Edwin

 

[Peter_SSs]

Hi Peter, in that case i'd like to have 210 as a result, ...

Because it is the highest of the two values, or
Because it is the first of the two values?

 

[edtwiest]

Because it is the highest of the two values, or
Because it is the first of the two values?

Because it's the highest one.

 

[Peter_SSs]

Because it's the highest one.

With 365:

=LET(f,FILTER(F$2:L$9,E$2:E$9=A2),a,ABS(f-B2),MAX(FILTER(f,a=MIN(a))))

 

[Peter_SSs]

With 2016:

=AGGREGATE(14,6,INDEX(F$2:L$9,MATCH(A2,E$2:E$9,0),0)/(ABS(INDEX(F$2:L$9,MATCH(A2,E$2:E$9,0),0)-B2)=MIN(ABS(INDEX(F$2:L$9,MATCH(A2,E$2:E$9,0),0)-B2))),1)