Multiple Matches - Index/Match

hello -

I have attached a simplified workbook which contains my problem.

Worksheet "BI" contains the data.
Worksheet "Main" displays data based on the given date (A3).

When "Main" is active, I would like the user to be able to enter a date into (A3) and have all corresponding 'Widgets' displayed in (A4) and all corresponding 'Units' to be displayed in (B4). If a second 'Unit' matches the given criteria (date) i would like that to be displayed in (A5) and (B5) respectively.

I am able to do this when only one 'Unit' matches the given criteria.
=INDEX(BI!A2:BI!A21,MATCH(A3,BI!B2:BI!B21,0))

Notice how Ship Date (BI!B10, and BI!B11) are the same, 7/25/2007. If I were to type in 7/25 to (Main!A3) I would like to see both 'Widgets' displayed along with their respective 'Units'.

May 24, 2007
BI0009 | 5
BI0010 | 5

Hope this makes sense, apologies if this has been answered before--i've searched but i can't find anything.

brdwlsh

Hi

In sheets Main:
B4: array enter (ctrl, shift, enter) the formula
=IF(ROW()-3>COUNTIF(BI!$B$2:$B$21,Main!$A$3),"",INDEX(BI!$A$1:$A$21,SMALL(IF(BI!$B$2:$B$21=Main!$A$3,ROW(BI!$B$2:$B$21)),ROW()-3)))
Copy down to B11.
C4: array Enter
=IF(ROW()-3>COUNTIF(BI!$B$2:$B$21,Main!$A$3),"",INDEX(BI!$D$1:$D$21,SMALL(IF(BI!$B$2:$B$21=Main!$A$3,ROW(BI!$B$2:$B$21)),ROW()-3)))
Copy down to C11.

HTH

rylo

thanks rylo.

i was able to make this work in my original attachment, i had to remove a couple extra spaces from the formula though.

i thought that i would be able to plug this in to another worksheet which has a different format, change the data ranges, and have everything work without smoothly. i was wrong. There must be something i'm missing.

anyway, i have attached a file which is identical in format to my real version. the formulas which i edited and thought would work are in [B10, C10] and should be carried through to [B14, C14].

There will be 4 other worksheets identical in structure to BI: LH, SL, CX, and CHi. I will adjust the formula to make it work for them too (at least that's the plan!)

i appreciate the help.

brdwlsh

Hi

Dashboard!B10:
=IF(ROW()-9>COUNTIF(BI!$C$9:$C$5000,DASHBOARD!$I$2),"",INDEX(BI!$B$1:$B$5000,SMALL(IF(BI!$C$9:$C$5000=DASHBOARD!$I$2,ROW(BI!$C$9:$C$5000)),ROW()-9)))

Dashboard!C10:
=IF(ROW()-9>COUNTIF(BI!$C$9:$C$5000,DASHBOARD!$I$2),"",INDEX(BI!$G$1:$G$5000,SMALL(IF(BI!$C$9:$C$5000=DASHBOARD!$I$2,ROW(BI!$C$9:$C$5000)),ROW()-9)))

The ROW()-n has to equal 1 for the first appearance of the formula. So if the formula appears in row 10, then n has to be 9. If it is in row 3, then n has to be 2.

Also, the range being indexed has to start from row 1. This is because the result from the if statement returns the row number. To make the index work the range has to start from row 1.

HTH

rylo

thanks for the effort -- it works perfectly.

brdwlsh