VLOOKUP date in date range

Question

Is there a simpler way to complete this task?

I want to look up a value and a date in a list and return the date if it matches or is within 5 days of the original date.

The only way I've been able to do this so far is to create a helper key (A1) and look up the helper-key in a helper-key list (F:F), and add an IFERROR to look it up again but add 1 and so on.

Example:

    A           B           C           D
1   table1_ky   table1_id   table1_dt   vlookup
2   99941275    999         1/1/2013    1/3/2013


    G           H           I
1   table2_ky   table2_id   table2_dt
2   99941277    999         1/3/2013

Formula in D2:

=IFERROR(VLOOKUP(TEXT(A2,0),F:H,3,0),IFERROR(VLOOKUP(TEXT(A2+1,0),F:H,3,0),IFERROR(VLOOKUP(TEXT(A2+2,0),F:H,3,0),"no")))

I want to be able to make the number of "+1"s variable so I can change the date range to 5, 10, 30 etc. without having to use up to 30 IFERROR statements.

Is there a function(s) in Excel that can do something like this?

I'd like to avoid using VBA if possible.

... Is your data in Ascending / Descending order of date or "random" — John Bustos

barry houdini barry houdini · Accepted Answer · 2013-03-21T22:06:05

You could use a LOOKUP formula like this

=IFERROR(LOOKUP(2,1/(G$2:G$100=B2)/(H$2:H$100>=C2)/(H$2:H$100<=C2+5),H$2:H$100),"no")

That will find the last date in H2:H100 which is between C2 and C2+5 and where the column G value also matches - doesn't need any "concatenated" helper columns and you can adjust the 5 to be any value you want.

By "last" I mean positionally - if you want the earliest date in the case of multiple matches (which is what your current formula does) then switch to an "array formula" like this:

=IFERROR(SMALL(IF((G$2:G$100=B2)*(H$2:H$100>=C2)*(H$2:H$100<=C2+5),H$2:H$100),1),"no")

confirmed with CTRL+SHIFT+ENTER

VLOOKUP date in date range

1 Answers