Multiple vlookup that includes date match between two dates

**dynamoracing** · 09-14-2013, 11:18 PM

I need some help with Multiple Vlookup, but one of the vlookups is a date that needs to be matched between two dates with -+ 7 day cushion.

How do I do something like it?

I have included an example spreadsheet with more information.

Example Multiple Vlookup.xlsx

Thank you in advance.

**FDibbins** · 09-15-2013, 01:02 AM

Based
on your date criteria, none of your data matches

you state that "<- I used this for valid example with date 6/20/13 on Form sheet"
but the date for that row that you highlighted was 6/10/2013 - your ctiteria is +- 7 days, to 6/13 to 6/17

Anyway, try this for what you want. Add a helper column on sheet2 (I used H, you can use what you want, and you can hide it too), and copy this down...
=IF(AND(D2>=Form!$D$4-7,D2<=Form!$D$4+7),H1+1,H1)
Then to find what you want, use this...
=IFERROR(INDEX(Data!A:B,MATCH(ROW(Data!A1),Data!H:H,0),2),"")
and this...
=IF(B14="","","Yes")

Let me know how you make out please?

**dynamoracing** · 09-15-2013, 09:41 PM

T-00436732 is a valid promotion because it matches the node, bsp, type and rate of my criteria and it starts on 6/10/13 with 7 day cushion 6/3/13 and ends on 7/8/13 and 7/15/13 with 7 day cushion, our criteria date of 6/20/13 falls between those days which makes it valid.

------------------------

If my criteria date is 6/1/13 and other criteria stays the same, then there is no promotion with dates that have 6/1/13 in between, so I want it to show that my criteria is not valid, but show a promotion that is closest to that date before criteria date which in this case is T-00436731 with cushion start date of 3/29/13 and cushion end date 5/22/13

I hope this makes sense, I'm adding another worksheet that has added cushion dates columns.

Example Multiple Vlookup2.xlsx