Index and Match with Two Criteria, and date Match Type is Less Than

Hello Experts,
The current array formula has two criteria. The first part matches the "100" ID. The second part matches the "May 21, 2013" as an exact match...resulting in "#N/A" because "100" and "May 21, 2013" does not exist.

How would you improve this array formula to add the Match Type of "1" Less Than in the second part? The "May 21, 2013" through to "June 19, 2013" needs to be converted to "May 20, 2013"...answer is cell B5.

Shift+Ctrl+Enter

Formula:

Please Login or Register  to view this content.

[A] [B]
ID# Bill Date [Row 1]
201 10-Apr-13 [Row 2]
100 20-Apr-13 [Row 3]
201 10-May-13 [Row 4]
100 20-May-13 [Row 5]
201 10-Jun-13 [Row 6]
100 20-Jun-13 [Row 7]

I've tried Sumproduct also but it's not right either. It only works up to the next date "June 10, 2013" and errors with a "0" because the ID# is "201" (no longer "100"). I need to match to "100" and if the Match date is between "May 21, 2013" to "June 19, 2013", the answer should be "May 20, 2013".

Formula:

Please Login or Register  to view this content.

Thanks in advance,
Ricky

Thank-you ChemistB!
Works like a charm. This is perfect!

Greatly appreciate it.

Regards,
Ricky

Originally Posted by ChemistB

Try this array formula

Formula:

Please Login or Register  to view this content.

Does that work for you?

Hi again Experts,
Could we add a third criteria to this array formula (credit goes to ChemistB)? Currently, this formula has two criteria.

Formula:

Please Login or Register  to view this content.

Column [F] is the new search criteria for "Yes".
C1 searches for ID#
D1 searches for Bill Date
E1 searches for Qualifier (“Pending” or “No” or “Yes”)

[A]...[B]............[F]
ID#...Bill Date.....Qualifier [Row 1]
201...10-Apr-13..Pending [Row 2]
100...20-Apr-13...No [Row 3]
201...10-May-13..Pending [Row 4]
100...20-May-13..No [Row 5]
100...20-May-13..Yes [Row 6]
201...10-Jun-13...Pending [Row 7]
100...20-Jun-13...No [Row 8]
201...10-Jul-13....Pending [Row 9]
100...20-Jul-13....Yes [Row 10]

Test results would look like this:
1) Search for ID# [100], Search for Bill Date [May 19, 2013 or any earlier date], Search for Qualifier [Yes] = #N/A error because no "Yes" qualifier exists before "May 20, 2013"
2) Search for ID# [100], Search for Bill Date [May 20, 2013 to July 19, 2013 inclusive], Search for Qualifier [Yes] = “May 20, 2013” cell B6
3) Search for ID# [100], Search for Bill Date [July 20, 2013 or any later date], Search for Qualifier [Yes] = “July 20, 2013” cell B10

I am continuing this thread to retain the history; rather than, starting a new thread. My apologies, if this isn't the proper protocol. Your help is greatly appreciate it.

Thanks in advance,
Ricky

Hi ratcat,
Here's the worksheet. Please advise.

Regards,
Ricky

Originally Posted by ratcat

G'day Ricky,

Uploading a small example of non sensitive information, with a before and after and this may fast track an answer.

Bump. Please help, Experts.

I've attached the sample spreadsheet. Please help revise the current Index and Match (with Match Type of Less Than) formula to add a third criteria..."Yes" based on the new "Qualifier" column.

Thanks in advance,
Ricky

Thank-you so much ChemistB,
You've come to my rescue yet again. This works nicely!

I appreciate it very much.

Regards,
Ricky

Originally Posted by ChemistB

Try arrayed

Formula:

Please Login or Register  to view this content.

Hi ChemistB,
I attempted to apply this to my workbook but unfortunately, my data is unsorted. I've searched for the workaround and found that the possible solution involves ABS and MIN. I'm unsure how to approach this. May I ask for your assistance again?

Regards,
Ricky

Hi ChemistB;
My real data has the dates are either in alphabetical order or from New to Old. Unfortunately, the dates and data can not be resorted. I've attached the sample spreadsheet.

There are 3 sheets:
Unsorted (formula works well)
Date (Old to New) formula works well
Date (New to Old) formula is giving the incorrect result compared to the other two sheets.

Could you please have a look? I'd appreciate it very much.

Regards,
Ricky

Originally Posted by ChemistB

That formula is not dependent on anything being in a particular order. There must be other issues with your worksheet. Try using "Evaluate Formula" which is found on Formulas Tab (probably using a small subset of your data).

Hi ChemistB,
If the data is unsorted, neither from smallest to large or vice versa, is there another more reliable function better than Match?

Regards,
Ricky