0

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.

Excel Screenshot for IDs, date ranges, and sample data

2
  • 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
1

=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.

4
  • 2
    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
  • 1
    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
0
=IFERROR(INDEX($D:$D, AGGREGATE(15,6,row($A$2:$A$4)/(($A$2:$A$4=F2)*($B$2:$B$4<=G2)*($C$2:$C$4>=G2)),1)),"NOT FOUND")

Take your AGGREGATE function and make a list of all the rows in your table. Divide that list by your criteria. When your criteria is TRUE it will be divide by 1 meaning no change. When one of the criteria is false, it will be divide by 0 which will throw and error which the 6 in AGGREGATE will ignore. 1 will pull the first row number from the sorted list. 15 sorts in Ascending order. Use 14 if you want to sort your acceptable rows in the opposite direction. The row number is then passed to INDEX which returns the cell address corresponding to the appropriate row in column D. If nothing is found in range and error is returned. IFERROR handles the error by returning "NOT FOUND".

Adjust your >= and <= to > and < to suit your definition of "between" the given date range.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.