How to determine the value from a list?

[Guest]

Does anyone have any suggestions on how to determine the value from a list?

There is a list of number on row 1
226 [A1], 230 [B1], 233 [C1], 238 [D1], ...

There is a number on row 2
228 [A2]

I would like to determine which max number on the list have the minimum
difference with the number 228 [A2]. In this case, the number having minimum
difference with 228 [A2] are 226 [A1] and 230 [B1], because both number have
the same difference 2.
228 - 226 = 2
230 - 228 = 2
then I prefer to select the max number 230 between 226 and 230, and return
this value in cell C1
Does anyone have any suggestions on how to do it in excel?
Thank in advance for any suggestions
Eric

 

[Bernd P]

Hello Eric,

Enter with CTRL + SHIFT + ENTER
=MAX(IF(ABS($A$1:$D$1-A2)=MIN(ABS($A$1:$D$1-A2)),$A$1:$D$1))

Regards,
Bernd

 

[Guest]

Thank you very much for your suggestions

Does anyone have any suggestions what the default value should be assign for
each empty cell?
I would like to assign the default value for if statement on row 1, such as
If(True, any number, default), but I cannot assign "" into formula
If(True, any number, ""), which does not work for following statement,
because of this character "".
Enter with CTRL + SHIFT + ENTER
=MAX(IF(ABS($A$1:$D$1-A2)=MIN(ABS($A$1:$D$1-A2)),$A$1:$D$1))
Does anyone have any suggestions?
Thank for any suggestions
Eric

 

[Guest]

try:

=MAX(IF($A$1:$D$1<>"",IF(ABS($A$1:$D$1-A2)=MIN(ABS($A$1:$D$1-A2)),$A$1:$D$1)))

to ignore empty cells

Enter with Ctrl+Shift+Enter