Look up one value and return multiple corresponding values

[Faye1986]

https://www.dropbox.com/s/e8hhx35o15sa314/Test Data.xlsx


I seem to be having trouble adding my spreadsheet so I have put a lin
to the sheet on dropbox above.

Can someone help I have a formula which is an index and small formula
however it works for the first two rows and then stops working and jus
returns blank.

I am looking for a formula that I can put into L3, P3 and R3 which wil
return the correct info for the corresponding ticket number from th
table to the left of the sheet. I can't use a simple vlookup as thi
will only the first row info for a specific ticket number when I nee
each item on a ticket.

I hope this makes sense, any help you can give on the formula would be
great help. I have tried to rectify it myself but I can't understan
why it only works for the first two rows and then stops.

Thank you

Fay

 

[Claus Busch]

Hi Faye,

Am Sun, 5 May 2013 22:17:41 +0100 schrieb Faye1986:

https://www.dropbox.com/s/e8hhx35o15sa314/Test Data.xlsx

I am looking for a formula that I can put into L3, P3 and R3 which will
return the correct info for the corresponding ticket number from the
table to the left of the sheet. I can't use a simple vlookup as this
will only the first row info for a specific ticket number when I need
each item on a ticket.

in L3 your range was not big enough. And for each new ticket number you
have to reset the counter for SMALL to 1.
In L3 try:
=IFERROR(INDEX($A$1:$G$12,SMALL(IF($D$1:$D$12=$J3,ROW($D$1:$D$12)),COUNTIF($J$3:J3,J3)),6),"")
In P3:
=INDEX($E$1:$E$12,MATCH(J3&L3,$D$1:$D$12&$F$1:$F$12,0))
And in R3:
=INDEX($G$1:$G$12,MATCH(J3&L3,$D$1:$D$12&$F$1:$F$12,0))


Regards
Claus Busch

 

[Claus Busch]

Hi Faye,

Am Mon, 6 May 2013 09:04:58 +0200 schrieb Claus Busch:

In L3 try:
=IFERROR(INDEX($A$1:$G$12,SMALL(IF($D$1:$D$12=$J3,ROW($D$1:$D$12)),COUNTIF($J$3:J3,J3)),6),"")
In P3:
=INDEX($E$1:$E$12,MATCH(J3&L3,$D$1:$D$12&$F$1:$F$12,0))
And in R3:
=INDEX($G$1:$G$12,MATCH(J3&L3,$D$1:$D$12&$F$1:$F$12,0))

all formulas are array formulas to enter with CTRL+Shift+Enter


Regards
Claus Busch