Vlookup same item but after certain date

[Excelsurf]

Hi,

I have a scenario where we have an items transaction, and we want to know the response date of that transaction. We have the below example:
Request Data:

Item	Transaction Date
ABC	1/8/2023​
DEF	12/7/2022​
XYZ	9/8/2022​
ABC	7-Oct​
ABC	4/7/2022​
ABC	9/15/2022​
ABC	6/3/2022​
ABC	1/25/2022​
XYZ	9/23/2022​
XYZ	10/22/2022​
DEF	4/22/2022​
XYZ	9/30/2022​

Response Data:

Item	Response Date
ABC	5/5/2022​
DEF	2/10/2023​
DEF	12/4/2022​
DEF	12/17/2022​
XYZ	3/4/2022​
ABC	10/1/2022​
ABC	9/27/2022​
ABC	9/14/2022​
DEF	10/25/2022​

Now we see Item ABC was transacted on 6/3. The first date AFTER 6/3 for item ABC in the "response data" is 9/14. Thus, we conclude that the question on item ABC was answered on 9/14. Example output below:

Item	Transaction Date	Response Date
ABC	1/8/2023​	N/A
DEF	12/7/2022​	12/17/2022​
XYZ	9/8/2022​	N/A
ABC	7-Oct​	N/A
ABC	4/7/2022​	5/5/2022​
ABC	9/15/2022​	10/1/2022​
ABC	6/3/2022​	10/1/2022​
ABC	1/25/2022​	N/A
XYZ	9/23/2022​	N/A
XYZ	10/22/2022​	N/A
DEF	4/22/2022​	12/4/2022​
XYZ	9/30/2022​	N/A

 

[awoohaw]

im confused with your data I see ABC multiple times, so is it correct to guess you were picking 6/3 as an arbitrary example? but then I notice in the request date grid that the dates are not in sequential order? Do you want to look up a combination that you already know like "ABC" & "9/3/2022" and find the next record after that date with that item name in the response date grid?

And what if there are intervening dates for the same item after 6/3/2022 and the desired response date?

 

[awoohaw]

also, you can help people here help you by using the xl2bb add in and post a mini workbook. See the link below, please.

 

[Excelsurf]

im confused with your data I see ABC multiple times, so is it correct to guess you were picking 6/3 as an arbitrary example? but then I notice in the request date grid that the dates are not in sequential order? Do you want to look up a combination that you already know like "ABC" & "9/3/2022" and find the next record after that date with that item name in the response date grid?

And what if there are intervening dates for the same item after 6/3/2022 and the desired response date?

Yes, it is very confusing data but the scenario is correct. The first table would represent a transaction. That transaction could have occurred many times across many dates without a response in the second table. The goal here is define the amount of time required to address that transaction. So if item ABC was transacted on 4/16, I want to know when that item had a response. This is very common in ecommerce

 

[awoohaw]

So essentially, ignore any transaction times in the first table after the first date that intervene to the response date?
If so, why aren't you using 1/25/2022 as the start of ABC request? what do the N/A's mean?

 

[Excelsurf]

So essentially, ignore any transaction times in the first table after the first date that intervene to the response date?
If so, why aren't you using 1/25/2022 as the start of ABC request? what do the N/A's mean?

NA's mean that there was not a transaction in the response table after the date in the request table. For instance, Item XYZ was requested on 9/30. We do not see a transaction AFTER 9/30 for item XYZ.

In essence, we are attempting to grab the first date AFTER the request date for a certain item. For a date does not occur after the request date, we can assume that request was never fulfilled.

 

[awoohaw]

The first record ABC and the second ABC have plenty of response data following those dates, yet you mark some NA? Why? what rule am I missing or don't get? Please tell me why you are ignoring all the ABC dates

And if you can please use the xl2bb add in so forum users can have mini worksheets of your work it is most helpful. Converting your dates that are in different formats and all in text is very difficult. The link to get the xl2bb is below.

 

[Excelsurf]

The first record ABC and the second ABC have plenty of response data following those dates, yet you mark some NA? Why? what rule am I missing or don't get? Please tell me why you are ignoring all the ABC dates

And if you can please use the xl2bb add in so forum users can have mini worksheets of your work it is most helpful. Converting your dates that are in different formats and all in text is very difficult. The link to get the xl2bb is below.

The first record ABC was transacted on 1/8/23. Notice that we do not have a response for item ABC past date 1/8/23. Hence, we can assume that the request on 1/8 was never fulfilled. Does that make sense?

 

[awoohaw]

Okay, I am guessing the third list is the first list but youve added the transaction dates
And what you want is the response date from the second list which is calculated as the next date from that records request date. And if not, put an NA in. The following mini workbook (please get the xl2bb add in. half of the time i've spent on this issue has been reformatting your dates).

I have differences, please help me out

WorkBook1.xlsx
	A	B	C	D	E	F
44	ABC	01/08/2023			NA
45	DEF	12/07/2022	12/17/2022		12/17/2022
46	XYZ	09/08/2022			NA
47	ABC	10/07/2022			NA
48	ABC	04/07/2022	05/05/2022		05/05/2022
49	ABC	09/15/2022	10/01/2022		09/27/2022
50	ABC	06/03/2022	10/01/2022		09/14/2022
51	ABC	01/25/2022			05/05/2022
52	XYZ	09/23/2022			NA
53	XYZ	10/22/2022			NA
54	DEF	04/22/2022	12/04/2022		10/25/2022
55	XYZ	09/30/2022			NA
Sheet10

Cell Formulas
Range	Formula
E44:E55	E44	= IF(SUM((--($A44=$A$17:$A$25))*(--($B44<$B$17:$B$25)))<1,"NA", 1/(MAX(IFERROR(1/((--($A44=$A$17:$A$25))*(--($B44<$B$17:$B$25))*($B$17:$B$25)),1/1000000))))

 

[Excelsurf]

Okay, I am guessing the third list is the first list but youve added the transaction dates
And what you want is the response date from the second list which is calculated as the next date from that records request date. And if not, put an NA in. The following mini workbook (please get the xl2bb add in. half of the time i've spent on this issue has been reformatting your dates).

I have differences, please help me out

WorkBook1.xlsx
	A	B	C	D	E	F
44	ABC	01/08/2023			NA
45	DEF	12/07/2022	12/17/2022		12/17/2022
46	XYZ	09/08/2022			NA
47	ABC	10/07/2022			NA
48	ABC	04/07/2022	05/05/2022		05/05/2022
49	ABC	09/15/2022	10/01/2022		09/27/2022
50	ABC	06/03/2022	10/01/2022		09/14/2022
51	ABC	01/25/2022			05/05/2022
52	XYZ	09/23/2022			NA
53	XYZ	10/22/2022			NA
54	DEF	04/22/2022	12/04/2022		10/25/2022
55	XYZ	09/30/2022			NA
Sheet10

Cell Formulas
Range	Formula
E44:E55	E44	= IF(SUM((--($A44=$A$17:$A$25))*(--($B44<$B$17:$B$25)))<1,"NA", 1/(MAX(IFERROR(1/((--($A44=$A$17:$A$25))*(--($B44<$B$17:$B$25))*($B$17:$B$25)),1/1000000))))

Thank you! I will review this on Monday, I am currently traveling. I will also download that extension as you recommended. I appreciate all your thought in to this.