Matching Data in Excel to Closest Grouping

[AryehBak]

Let's say I have data in column A and categories in columns B, C, D.
want to take the data I have in column A and assign it one of th
values in the three following columns.

So, for instance, A1 = 10,000 B1 = 20,000 C1 = 12,000 and D1 = 6,000

I'd like E1 to have a formula that took the value in A1 (10,000) an
determined to which of B1, C1 and D1 it was closest and give me tha
value (in this case it should give me 12,000).

Is this doable?

THANKS

 

[jeff]

Hi,

I believe Frank Kabel or some of the others will have
a much more elequent solution, but this seems to work:

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

( a HLookup neede to be in sorted order and I wasn't sure
your data would be.)

jeff