1

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/

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
Nathanial Wcislak
  • 33
  • 4
  • Can you please tell us your ultimate goal instead of the steps you would like to take? Don't want to fall into the [X Y problem trap](http://meta.stackexchange.com/questions/66377/what-is-the-xy-problem). – Kyle Sep 28 '16 at 19:31
  • 1
    The end goal would be to pull the number and the value (banana or apple). The tricky part for me is returning the value that has the closest date match. for instance, 101 would return Banana because the dates are closer together – Nathanial Wcislak Sep 28 '16 at 19:34

1 Answers1

1

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

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • 1
    I'm sorry for the incredibly late reply on this, work had sent me out. This has worked for me so far, I tried using a vlookup, but the value would sometimes return as completely wrong. Scott, is there a weblink you use to learn more excel? – Nathanial Wcislak Nov 01 '16 at 00:34