Data lookup

I have 2 worksheets. WkSht1 has 1 column

    A                    

Client name     

Bob                 

Tom 

WKSHT 2 has two columns Col. B has multiple client IDs per Client in Col. A

A                    B

CLIENT         CLIENT ID

Bob               B123

                     B456

                     B789

Tom             T321

                    T654

                    T987          

There are variable numbers of IDs per Client

I need a lookup to match on the client from WKSHT1 and retrieve all of the client's IDs from WKSHT2 to seperate cells on WKSHT1

A                       B                 C           D       >>>>>>

CLIENT          ID1               ID2         ID3     >>>>>

Bob                B123           B456       B789   >>>>>

I've tried VLOOKUP which will retrieve the first or last ID (true/False) but not all of them.

Any suggestions?

Thanks I'll pass the help on when I get chance!

It would make life far simpler if you replicated the Client name on WKSHT 2

 Fishin

This will likely require code. Before we take a stab at it perhaps I could ask a couple more questions.

1. Is this a 'one-off'?
2. Are the client names unique? (e.g only one Bob?)

Hi,

Provided the client names are unique, then I believe that you can achieve this with formulas.

This answer assumes that the data starts in cell A2 with the numbers in column B, and that there are no gaps.

In sheet 2 in column C row 2 you need to enter  =IF(A2="",C1,A2)

In sheet 2 in column D row 2 you need to enter  =B2

Then in sheet 1 I had the following set up:

In cells C1 to K1 enter values 1 to 9 sequentially.

Cell A2 is where you enter the query you want an answer for

In Cell B2 enter =COUNTIF(Sheet2!$C$2:$C$12,A2)-1

In Cell C2 enter =INDIRECT("Sheet2!D"&MATCH($A2,Sheet2!$C$2:$C$12,0)+C$1)

Drag cell C2 across to cell K2.

This answer assumes that there are no more that 9 codes per member - if there are more then expand teh number range in row 1 to match the anticipated number, and drag cell K2 across to the last column with a value in it.

There might be a more elegant way to do it, but I have used this before without any problems.