CODEWITHSUNDEEP
excelexcel-2010
Nathanial Wcislak
Nathanial Wcislak

Reputation: 33

Excel, find a date closest in a range of dates given a number and return another cell

New to advanced excel concepts, I have a list of numbers such as:

101 02/22/2016  
100 02/21/2016

and then another list like so:

101 01/01/2016 Apple
101 02/20/2016 Banana
100 02/21/2016 Apple
100 02/23/2016 Banana

I'm trying to get it where I use a vlookup with the number on the more basic table, check for a match on the advanced table then find the date on the advanced table that is closest to the date on the basic table, then return the value to the right (Banana, Apple).

I've got the vlookup part down, but placing an if statement just returns N/A and breaks every time.

I've also tried using this guide: http://eimagine.com/say-goodbye-to-vlookup-and-hello-to-index-match/

Upvotes: 1

Views: 1297

Answers (1)

Scott Craner
Scott Craner

Reputation: 152660

You can use this array formula:

=INDEX($C$1:$C$4,MATCH(MIN(IF($A$1:$A$4=E1,ABS($B$1:$B$4-F1))),IF($A$1:$A$4=E1,ABS($B$1:$B$4-F1)),0))

Being an array formula it needs to be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode. If done correctly then Excel will put {} around the formula.

enter image description here

Upvotes: 1

Related Questions