[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 461
  • Last Modified:

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 :)
0
Ross
Asked:
Ross
1 Solution
 
krishnakrkcCommented:
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now