MS Excel closest match lookup

Take this example:

                              
                      Alison      Natalie      Ross              Actual      Mins Late                 Closest
Date                                    
10/01/2013      09:15      09:06      09:09      09:08      8                      Ross
11/01/2013      09:03      09:09      09:06      09:06      6                      Ross


I want to automatically put the name of the person who was closest to the estimated time in the "closest" cell once a value is entered in the "actual" time field. IE, check that days times, compare to "actual", and grab the name of who's "closest".

I'm completely stuck with how to do this - I guess it's something to do with a vlookup and an index, but I've never used index!

Thanks all :)
LVL 2
RossAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
krishnakrkcConnect With a Mentor Commented:
HI

=INDEX($B$1:$D$1,MATCH(MIN(ABS(E3-B3:D3)),ABS(E3-B3:D3),0))

it's an array formula. Confirmed with CTRL + SHIFT + ENTER

where E3 holds actual time
B1:D1 names, B3:D3 times

Kris
0
 
RossAuthor Commented:
thank you that's great :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.