Vlookup to return multiple row from different worksheet

Hi,

I'm sure this question has been ask multiple times, so I apologize for asking it again :D I tried to google this and found a few suggested solutions, but for some reason I'm having trouble applying them to my scenario.

So I have this workbook containing 2 spreadsheets -> Comp and Daily Report

The comp sheet has information, combination of text and numbers, entered starting with the date it occured. It's a rolling sheet for the entire month. The daily report sheet is supposed to be the print friendly, per day version of this sheet. It uses lookup to pull information from the comp sheet based on date entered on the daily report in D2. Formula looks like this:

(in cell B5): =LOOKUP($D$2,Comps!A2:A100,Comps!B2:B100)
(in cell C5): =LOOKUP($D$2,Comps!A2:A100,Comps!C2:C100)
(in cell D5): =LOOKUP($D$2,Comps!A2:A100,Comps!D2:D100)
(in cell E5): =LOOKUP($D$2,Comps!A2:A100,Comps!E2:E100)


Now that works all nice and shiny, but what if the comp sheet has more than one row for the same date? That is where my problem lies. So I would want to have the first instance in row 5 on the daily report, the 2nd instance in row 6, next in row 7 and so on and so forth.

Hope that makes any sense :D here some screenshots comps.JPGdaily report.JPG

How would I accomplish that?

Thanks so much for the help!

See if attached works for you...

[xls generated by XL2010]

Originally Posted by jhren

See if attached works for you...

[xls generated by XL2010]

Exactly what I was looking for! Thanks so much Jhren

You're Welcome...!!!

Originally Posted by jhren

See if attached works for you...

[xls generated by XL2010]

=IF(ISERROR(INDEX(Comps!B$2:B$1000,SMALL(IF(Comps!$A$2:$A$1000=$D$2,ROW(Comps!$A$2:$A$1000)-1,10^10),ROW()-4))),"",INDEX(Comps!B$2:B$1000,SMALL(IF(Comps!$A$2:$A$1000=$D$2,ROW(Comps!$A$2:$A$1000)-1,10^10),ROW()-4)))

@jhren, don't use error trap the whole formula. It looks twice as long.


Try it like this...

=LOOKUP("zzz",CHOOSE({1,2},"",INDEX(Comps!B:B,SMALL(IF(Comps!$A$2:$A$52=$D$2,ROW(Comps!B$2:B$52)),ROWS(B$5:B5)))))


Array formula: Press Ctrl+Shift+Enter, not just Enter

Originally Posted by Teethless mama

@jhren, don't use error trap the whole formula. It looks twice as long.

Try it like this...

=LOOKUP("zzz",CHOOSE({1,2},"",INDEX(Comps!B:B,SMALL(IF(Comps!$A$2:$A$52=$D$2,ROW(Comps!B$2:B$52)),ROWS(B$5:B5)))))


Array formula: Press Ctrl+Shift+Enter, not just Enter

I've seen that method before, but don't have need to use often enough to remember it.

In 2010 I use...

=IFERROR(INDEX(Comps!B$2:B$1000,SMALL(IF(Comps!$A$2:$A$1000=$D$2,ROW(Comps!$A$2:$A$1000)-1,10^10),ROW()-4)),"")