I would suggest, then, that you insert a new column A in Sheet2 and
put this formula in the new A1:

=IF(B1="","-",B1&"_"&COUNTIF(B$1:B1,B1))

Then copy this down as far as you need (or even further - it will show
"-" in the cell if you have no data in column B, but this won't affect
things).

Then in B1 of Sheet1 you can use this formula:

=IF(ISNA(MATCH($A1&"_"&COLUMN(A1),Sheet2!$A:$A,0)) ,"",INDEX(Sheet2!$C:
$C,MATCH($A1&"_"&COLUMN(A1),Sheet2!$A:$A,0))

Then you can copy this across as far as you think you need (eg if
there might be 10 people with Andie as a name, you will need to copy
it that far across, but it doesn't matter if you copy it further).
Then copy these cells down to cover your names.

Hope this helps.

Pete

On Feb 6, 11:59*am, Maria wrote:
Hi Pete,

yes, exactly!



"Pete_UK" wrote:
How do you want them to appear in Sheet1? Do you want to get Smith in
column B and Robertson in column C then Berg in column D etc?

Pete

On Feb 6, 10:15 am, Maria wrote:
Hi all,

I have the same problem.

I am trying to lookup the value of one sheet, in another sheet.

Example:

Sheet1:

Column A
Andie
Sam
Mary
John
Carlos

Sheet2:

Column A * *Column B
Andie * * * * *Smith
Claudia * * * Mensi
Michele * * * Castro
Andie * * * * *Robertson
Mary * * * * *Johnson
Andie * * * * Berg

So I want to look up Andie in sheet2, and get the multiple results in sheet
1. Any ideas?

Many thanks!- Hide quoted text -

- Show quoted text -