Finding a close match

  • Thread starter Thread starter mailrail
  • Start date Start date
M

mailrail

I have four time values in a row. The first value is a specific chosen time
value. The next three are demonstrated times. I need to find the closest
match of the three demonstrated times to the first column. Any help?
 
One way


=INDEX(B1:D1,MATCH(MIN(IF(ABS(B1:D1-A1)=MIN(ABS(B1:D1-A1)),B1:D1)),B1:D1,0))


entered with ctrl + shift & enter

where B1:D1 are the 4 values and A1 the chosen value



--


Regards,


Peo Sjoblom
 
Hi,

Here is another solution:

=INDEX(T,MATCH(MIN(ABS(T-A1)),ABS(T-A1),0))

I have named the range A2:A4, where your three demonstration times are, T.
You don't need to use the name, just substitute the address. A1 is the cell
containing the time you want to check.

This formula is an array, which means you must press Shift+Ctrl+Enter to
enter it.

Remember to format the cell to a time format.
 
Back
Top