Find multiple results with INDEX & MATCH on 2 criteria

Hi all,

I think I'm pushing it with this one but thought I'd ask in case there is some very clever cookie in here.

I have an index & match based on two criteria which is working fine:

Please Login or Register  to view this content.

The problem is that there may be multiple instances with different results, so I would like to have the ability to look for and return the 1st result, 2nd, 3rd etc, knowing that sometimes only one result is available. I found the following formula on another website but can't figure out for the life of me how to combine the two:

Please Login or Register  to view this content.

Thanks in advance to anyone who has a suggestion.
IM

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Thanks for the suggestion John and apologies for not doing it before.

So in my workbook I have the following:
. WBK 1 - Raw Data: separate workbook which will store the data that I look into. Currenty contains ~2000 lines and will continue to grow over time
. WBK 2 - Before: format of the workbook that will contain the macro where I would like to input data
. WBK 2 - After (current): data is being imported with a Index & match based on two match criteria (X-number & "Released") however it only returns one date
. WBK 2 - After (ideal): the way I would like data to be imported, where it would return (when available) the 1st, 2nd, 3rd, etc result down the list.

Thanks in advance to anyone who can help.
IM

The results do not tally with the data: assuming "OP Start Date"="Schedule start date" then they do not match in all cases.

Use consistent terminology: Either "OP" or "Schedule" (assuming the same).

Item/Req/PO= Asset number (minus the "X-") ? ... this always the case?

Try

BUT data needs to be sorted by "Asset Number" if you require the sequence 1 to n.

In "AFTER (Ideal")

in B5

=IFERROR(INDEX(RIGHT('WBK 1 - Raw Data'!$A$4:$A$100,9),SMALL(IF(('WBK 1 - Raw Data'!$G$4:$G$100="Released")*(SMALL('WBK 1 - Raw Data'!$E$4:$E$110,ROW($A$2:A2))),ROW('WBK 1 - Raw Data'!$A$4:$A$100)-ROW($A$4)+1,""),ROWS($A$4:A4))),"")

in C5

=IFERROR(INDEX('WBK 1 - Raw Data'!$E$4:$E$100,SMALL(IF('WBK 1 - Raw Data'!$G$4:$G$100="Released",ROW('WBK 1 - Raw Data'!$A$4:$A$100)-ROW($A$4)+1,""),ROWS($A$4:B4))),"")

in D5

=COUNTIF($B$5:B5,B5)

In E5


=IFERROR(INDEX('WBK 1 - Raw Data'!$F$4:$F$100,SMALL(IF('WBK 1 - Raw Data'!$G$4:$G$100="Released",ROW('WBK 1 - Raw Data'!$A$4:$A$100)-ROW($A$4)+1,""),ROWS($A$4:B4))),"")

Hi John,

My apologies, I am so familiar with the terminology that I didn't realize I wasn't consistent across the sheets.

I have reattached the file with correct headings. I need to clarify two things:

. I only need formulas in the colored cells
. I can't sort WBK 1 - Raw Data by Asset Number as I don't own the file and it is extracted from Oracle via a DB Query. It is sorted by "Sched Start Date" in column E, which is not needed for anything and does not link to "Op Start Date" in column C of WBK 2.

I hope this clarifies what I am facing

Thanks,
IM

Try

=IFERROR(INDEX('WBK 1 - Raw Data'!$F$4:$F$11,SMALL(IF(('WBK 1 - Raw Data'!$A$4:$A$11="X-" &$B5)*('WBK 1 - Raw Data'!$G$4:$G$11="Released"),ROW('WBK 1 - Raw Data'!$A$4:$A$11)-ROW($A$4)+1,""),$D5)),"")

It uses the sequence number in column D to find the correct value

This is brilliant, thank you It works perfectly.