Reputation: 33
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
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.
Upvotes: 1