Closest Match Formula

[TJAC]

Let me start with an example of data first...

12 25 2 9 ?

I have 4 columns above. The first three columns are numbers and one of
those needs go into a new column, which would be column 5. In column 5, I
need to know which of the first three columns is the closest match to column
4 without going over. Can a formula be set up like this? This would be in
Excel 2003.

Thanks!
Trisha

 

[cm]

assuming values are in a1,b1,c1 and d1:

=MAX(IF(A1>D1,0,A1),IF(B1>D1,0,B1),IF(C1>D1,0,C1))

 

[KC]

what if the data looks like...

12 6 2 9 ?

do you want it to be 12 or 6? because both have difference of 3 from 9?

-kc

 

[KC]

the below formula gets you the first closest to D1

=IF(ABS(D1-A1)=MIN(ABS(D1-A1),ABS(D1-B1),ABS(D1-C1)),A1,IF(ABS(D1-B1)=MIN(ABS(D1-A1),ABS(D1-B1),ABS(D1-C1)),B1,C1))


-kc
*Click Yes if this helps