Look up one value and return multiple corresponding values
 

https://www.dropbox.com/s/e8hhx35o15...st%20Data.xlsx


I seem to be having trouble adding my spreadsheet so I have put a link 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 just returns blank.

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.

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

Thank you

Faye

Look up one value and return multiple corresponding values
 

Hi Faye,

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

https://www.dropbox.com/s/e8hhx35o15...st%20Data.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$1 2,0))
And in R3:
=INDEX($G$1:$G$12,MATCH(J3&L3,$D$1:$D$12&$F$1:$F$1 2,0))


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Look up one value and return multiple corresponding values
 

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$1 2,0))
And in R3:
=INDEX($G$1:$G$12,MATCH(J3&L3,$D$1:$D$12&$F$1:$F$1 2,0))

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


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2