# Formula for Index/Match with multiple criteria WITH date range EXCEL

I am attempting to write a formula that will help pull data from column D into column H (currently populated but would be blank).

Logic is as follows: If any of the IDs in column A are in the range of column F and G2 is within the date range of B2 and C2, then give me the data from column D into H.

If ID's match and the date in question falls between or is equal to the date range for the matching ID, then pull the corresponding data in column D.

• What do you want to be returned when the value is not found within the criteria of your table? – Forward Ed Jul 9 '19 at 0:40
• #N/A would be fine – user1057669 Jul 9 '19 at 2:21

=IF(AND(F2=A2,AND(G2>=B2,G2<=C2)),D2,"")

F2=A2 checks that the ID's match
G2>=B2 checks that the date in G2 is more than B2*
G2<=C2 checks that the date in H2 is less than C2*
D2 sets the H2 value to D2 value if TRUE
"" sets the H2 value to blank if FALSE

Feel free to change "" to the value you want to display if it doesn't match.

Change to =IF(AND(F2=\$A\$2,AND(G2>=\$B\$2,G2<=\$C\$2)),\$D\$2,"") to tell cell G3 & G4 to compare to cells A2 to D2

*Assuming that G2 should be between B2 and C2 including endpoints as mentioned by @fixer1234. If you want to exclude endpoints just remove the equals sign.

• Just a note to think about the date range endpoints. If by "between", that means including the endpoints, you would want G2>=B2 and G2<=C2. – fixer1234 Jul 9 '19 at 1:20
• If I may make a suggestion, if you place a blank line between your formula your explanation it makes it stand out more and easier to read. – Forward Ed Jul 9 '19 at 1:20
• Thanks, Moshe. So the IDs should actually search within a range (my mistake above!). Would the correction to the formula be F2=A:A? – user1057669 Jul 9 '19 at 3:04
• Add that into your question to make it clearer. F2=A:A would not work as you need to check just one row where A = F. Have you tried @Forward Ed answer? – Moshe Gross Jul 9 '19 at 11:50