Track in and out sheet to find nearest return date based on send out date

[kwangyeow]

Hi All,

I'm new here and needed some help with excel formula related to index match with 2 criteria and after found the item, get the nearest date base the 2 criteria row date. As this outgoing sheet is continuously updating from day 1. There are some item that will be send out again after return from previous sending. But my formula only able to get the first instance date after it match the field Model and Serial. But this isn't what I want, since I want to find the nearest return date based on the send out date.

Outgoing sheet tab:
Column A : Date which send out item.
Column E and F are model and serial no. respectively.
Column J : return date (index and match from incoming sheet tab)

Incoming sheet tab:
Column A : Date which send out item.
Column E and F are model and serial no. respectively.

Below is the formula on the outgoing tab sheet I had written after extensively goggling. It check for model and serial no. field if empty, display empty. And if not empty, match the date return but it return the first date instead of the nearest date. I can't figure how to insert in the IF to show display valid date if found return date later than send out date.

=IF(AND(E266<>"",F266<>""),IF(ISNA(INDEX(Incoming!A:A,MATCH(E266&F266,Incoming!E:E&Incoming!F:F,0))),"",(INDEX(Incoming!A:A,MATCH(E266&F266,Incoming!E:E&Incoming!F:F,0)))),"")

Hope someone can help me with this... Much appreciated in advance !

Regards,
Kwang Yeow

 

[ExcelSpeedster]

Please post your workbook example so that we can assist with the issue. You can post a link from any cloud storage solution (Google Drive, OneDrive, etc.).

 

[kwangyeow]

Hi again,

this is the sample file which I need to get the valid date based on the send out date instead of the past old date. I'm doing index and match to determine correct item to retrieve the date and with that return date value, determine if it is return or not yet.

Below is the link to the sample file :


https://drive.google.com/file/d/0B_Ar7LSzp7LpVFRUMG5rQ20tOG8/view?usp=sharing

Once again, thanks for advising. Cheers !

Kwang Yeow.

 

[ExcelSpeedster]

Kwang, I understand your issue and I can see a proposed solution, but it would require building a macro to do this. Is that an option as a solution?

Formula-driven lookups require too many iterations to be taken care of, whereas this can be easily resolved in a macro.

 

[kwangyeow]

Hi ExcelSpeedster,

I like to know how macro can help with this solution if it can help to speed up. Currently this portion is only part of the whole excel sheet data and it's causing lots of calculation timing problem for me.
Thanks again !

Kwang Yeow

 

[kwangyeow]

Hi guys, anyone can help this problem ? I'm still looking for solution but can't seem to figure out.

 

[ExcelSpeedster]

I found a formula that fixes your issue.

Place this formula in J15 in the Outgoing sheet:

=IF(AND(E15<>"",F15<>""),IF(ISNA(INDEX(Incoming!A:A,MATCH(E15&F15,IF(Incoming!A:A>Outgoing!A15,Incoming!E:E&Incoming!F:F,""),0))),"",(INDEX(Incoming!A:A,MATCH(E15&F15,IF(Incoming!A:A>Outgoing!A15,Incoming!E:E&Incoming!F:F,""),0)))),"")

You must press Ctrl+Shift+Enter after pasting in the formula (instead of just pressing Enter) as it must be evaluated as an array formula.