Thread: Looking for mutiple values

I have two sheets in workbook. Sheet 1 has customer name, address etc, in column C is the customer id number.
Sheet 2 has a list of customers who have used a service, when they used it etc, I can use a Vlookup easy enough when they have only used one service but I need to bring in all instances of that customer.

I am struggling to articulate this! I have attached an example workbook in the hope that it will become clear!

What I would like to have Sheet 1 showing:

D2 = Consultation
E2 = John
F2 = 01/01/12
G2 = Room Hire
H2 = Sally
I2 = 05/01/2012
etc

D3 = Booking
E3 = Sally
F3 = 16/12/11
etc

I tried using a macro to find the value of C in sheet 2, resize, copy, paste in sheet 1, re-find and delete entire row. A bit clumsy I know but all I could think of, unfortunately I can't seem to get it to loop through all values of column C.

Sorry if this is a bit basic.

Thanks,

Dean

Try this:

Add this formula first to D2:

=COUNTIF(Sheet2!$A$2:$A$7,$C2)

and copy down

then in E2

=IF(COLUMNS($E$1:E$1)/3>$D2,"",INDEX(Sheet2!$B$2:$D$7,SMALL(IF(Sheet2!$A$2:$A$7=$C2,ROW(Sheet2!$B$2:$D$7)-MIN(ROW(Sheet2!$B$2:$D$7))+1),MOD(INT((COLUMNS($E$1:E$1)-1)/3+1)-1,3)+1),MOD(COLUMNS($E$1:E$1)-1,3)+1))

you must confirm this formula with CTRL+SHIFT+ENTER not just ENTER, then copy across as far as needed and down as far as needed.

Note: You will need to change all the $7's to correspond to last row in your database range.... you can do that by using Edit|Replace and replace $7 with $(lastrow#).

Try not to have too many rows though as this is an array formula and is not very efficient....

You will also need to format the date columns as dates....

From PM:

It's working beautifully for the first 3 instances but when I get to instance number 4 or 5 it goes back to the first one again. Is it currently limited to only find three instances? If so, which of the 3's do I need to change?

I apologize, I neglected to take into account possibility of more than 3 returns.

Try changing formula in E2 to:

=IF(COLUMNS($E1:E1)/3>$D2,"",INDEX(Sheet2!$B$2:$D$7,SMALL(IF(Sheet2!$A$2:$A$7=$C2,ROW(Sheet2!$B$2:$D$7)-MIN(ROW(Sheet2!$B$2:$D$7))+1),MOD(INT((COLUMNS($E$1:E$1)-1)/3+1)-1,$D2)+1),MOD(COLUMNS($E$1:E$1)-1,3)+1))

Confirmed with CTRL+SHIFT+ENTER not just ENTER.

copied down and across.