Return Vlookup Value if a match is found and date is between 2 values

[mikeb3408]

I have a table similar to below

Event Date	ID	Vlookup Info
2/13/2016	A156	X
1/15/2013	B498

<tbody>
</tbody>

I need to do a Vlookup to another table which has multiple lines of information about the IDs above, and I only want to return the line with info where the ID is matched and the Event Date falls between the Beginning Date and End Date in this other table as shown below:

ID	Beginning Date	End Date	Info
A156	2/10/2016	2/14/2016	X
A156	1/1/2015	1/15/2015	Y

<tbody>
</tbody>

So for in my example above, I would only want to retrieve the Information for the first line since the ID is matched and the Date falls within the range.

I assume I need a combination of Vlookup and If but not exactly sure how to proceed.

 

[NC Training Services]

Hi. I would combine an AND in an IF then the vlookup similar to as follows,

IF(AND(a1>date1,b1<date2),vlookup(enter vlookup info),"")

the AND tells the if you have multiple criteria, separate each criteria by a , if those conditions are met perform the vlookup, otherwise "" means blank, display a blank cell.

 

[NC Training Services]

the full formula didn't post through for some reason, IF(AND(cell reference > date 1, cell reference < date 2),vlookup(enter vlookup info),"").

hope this posts

 

[BobUmlas]

If your 2nd table is in G1:J100, for example. put this formula in the first table, 3rd column (assuming that table is in A1:C3, you'd put this in C2)
(Ctrl+Shift+enter):
=IF(MAX(ROW($1:$100)*(A2>=$H$1:$H$100)*(A2<=$I$1:$I$100)*(B2=$G$1:$G$100))=0,"",INDEX(J:J,MAX(ROW($1:$100)*(A2>=$H$1:$H$100)*(A2<=$I$1:$I$100)*(B2=$G$1:$G$100))))
And you can fill down.

 

[Aladin Akyurek]

A1:D3 of Sheet2 houses the data (the look up table).

In C2 of Sheet1 control+shift+enter, not just enter, and copy down:

=IFNA(INDEX(Sheet2!$D$2:$D$3,MATCH(1,IF(Sheet2!$A$2:$A$3=$B2,IF($A2>=Sheet2!$B$2:$B$3,IF($A2<=Sheet2!$C$2:$C$3,1))),0)),"")

If your system does not recognize IFNA, replace IFNA with IFERROR.

 

[mikeb3408]

If your 2nd table is in G1:J100, for example. put this formula in the first table, 3rd column (assuming that table is in A1:C3, you'd put this in C2)
(Ctrl+Shift+enter):
=IF(MAX(ROW($1:$100)*(A2>=$H$1:$H$100)*(A2<=$I$1:$I$100)*(B2=$G$1:$G$100))=0,"",INDEX(J:J,MAX(ROW($1:$100)*(A2>=$H$1:$H$100)*(A2<=$I$1:$I$100)*(B2=$G$1:$G$100))))
And you can fill down.

Thank you for your response.

In this case my first table is actually A1:O41 with my Event Date in Column A and ID in Column C. I need to pu my vlookup into Column S.

My other table is actually from A1:X6 with the
ID in Column A,
Begin Date in Column E,
End Date in Column F

And I want to return Column K in my original v lookup.

How would this change your formula?

Thanks!

 

[mikeb3408]

A1:D3 of Sheet2 houses the data (the look up table).

In C2 of Sheet1 control+shift+enter, not just enter, and copy down:

=IFNA(INDEX(Sheet2!$D$2:$D$3,MATCH(1,IF(Sheet2!$A$2:$A$3=$B2,IF($A2>=Sheet2!$B$2:$B$3,IF($A2<=Sheet2!$C$2:$C$3,1))),0)),"")

If your system does not recognize IFNA, replace IFNA with IFERROR.

I am trying your solution right now and think I am close, this is what I have right now:

=IFNA(INDEX(Sheet3!$A$2:$X$6,MATCH(1,IF(Sheet3!$A$2:$A$6=$C8,IF($A8>=Sheet3!$E$2:$E$6,IF($A2<=Sheet3!$F$2:$F$6,1))),0)),"")

Here is more info about my actual sheets, can you help me adjust?

In this case my first table is actually A1:O41 with my Event Date in Column A and ID in Column C. I need to pu my vlookup into Column S.

My other table is actually from A1:X6 with the
ID in Column A,
Begin Date in Column E,
End Date in Column F

And I want to return Column K in my original v lookup.

Right now my formula is giving me a null

 

[Aladin Akyurek]

I am trying your solution right now and think I am close, this is what I have right now:

=IFNA(INDEX(Sheet3!$A$2:$X$6,MATCH(1,IF(Sheet3!$A$2:$A$6=$C8,IF($A8>=Sheet3!$E$2:$E$6,IF($A2<=Sheet3!$F$2:$F$6,1))),0)),"")

Here is more info about my actual sheets, can you help me adjust?

In this case my first table is actually A1:O41 with my Event Date in Column A and ID in Column C. I need to pu my vlookup into Column S.

My other table is actually from A1:X6 with the
ID in Column A,
Begin Date in Column E,
End Date in Column F

And I want to return Column K in my original v lookup.

Right now my formula is giving me a null

Here is the workbook of the set up: https://www.dropbox.com/s/xj6ruc27y8f1nto/mikeb3408.xlsx?dl=0
which, I'm sure, you can adjust yourself to your lay-out.

 

[mikeb3408]

Here is the workbook of the set up: https://www.dropbox.com/s/xj6ruc27y8f1nto/mikeb3408.xlsx?dl=0
which, I'm sure, you can adjust yourself to your lay-out.

I got it working! Thank you so much for your help

 

[Aladin Akyurek]

I got it working! Thank you so much for your help

Glad to hear that. Thanks much for the update.