vlookup for same name, multiple instances...
 

I want to return the 2nd field for Jim in a single list:

Jim Dell
Jeff HP
Carl Compaq
Jim Toshiba
Carl Sony
Jim Lenovo
Jeff IBM

I want to pull Jim's accounts to another worksheet:

HP
Toshiba
Lenovo

When I use vlookup, it just returns HP and stops. How do I tell it to
return the next instance of Jim?

Chris_Hatch wrote:
I want to return the 2nd field for Jim in a single list:

Jim Dell
Jeff HP
Carl Compaq
Jim Toshiba
Carl Sony
Jim Lenovo
Jeff IBM

I want to pull Jim's accounts to another worksheet:

HP
Toshiba
Lenovo

When I use vlookup, it just returns HP and stops. How do I tell it to
return the next instance of Jim?

If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook you can
array enter

=VLookups("Jim",dataRange,2)

Alan Beban

Alan,

I get a #NAME? error when I put in your formula. I noticed you used
"vlookups" instead of "vlookup" and you removed the "false" qualifier. Is
that part of the solution?

When I do =vlookup(Jim,range,2,false) it returns Dell. I want to the next
time I put a vlookup command to return Toshiba and then Lenovo respectively.
The way I have it now just keeps returning HP. How do I tell it to go to the
next Jim's column two value?

"Alan Beban" wrote:

Chris_Hatch wrote:
I want to return the 2nd field for Jim in a single list:

Jim Dell
Jeff HP
Carl Compaq
Jim Toshiba
Carl Sony
Jim Lenovo
Jeff IBM

I want to pull Jim's accounts to another worksheet:

HP
Toshiba
Lenovo

When I use vlookup, it just returns HP and stops. How do I tell it to
return the next instance of Jim?

If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook you can
array enter

=VLookups("Jim",dataRange,2)

Alan Beban

You have to download from the link Alan provided to use the Vlookups
Function--it's not standard in Excel.

tj

"Chris_Hatch" wrote:

Alan,

I get a #NAME? error when I put in your formula. I noticed you used
"vlookups" instead of "vlookup" and you removed the "false" qualifier. Is
that part of the solution?

When I do =vlookup(Jim,range,2,false) it returns Dell. I want to the next
time I put a vlookup command to return Toshiba and then Lenovo respectively.
The way I have it now just keeps returning HP. How do I tell it to go to the
next Jim's column two value?

"Alan Beban" wrote:

Chris_Hatch wrote:
I want to return the 2nd field for Jim in a single list:

Jim Dell
Jeff HP
Carl Compaq
Jim Toshiba
Carl Sony
Jim Lenovo
Jeff IBM

I want to pull Jim's accounts to another worksheet:

HP
Toshiba
Lenovo

When I use vlookup, it just returns HP and stops. How do I tell it to
return the next instance of Jim?

If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook you can
array enter

=VLookups("Jim",dataRange,2)

Alan Beban

You could try this formula using resident XL functions:

Names in Column A
Companies in Column B

Name to lookup in C1

Enter this array formula in D1:

=INDEX($B$1:$B$30,SMALL(IF($A$1:$A$30=$C$1,ROW($A$ 1:$A$30)),ROW(A1)))

Must be entered using <Ctrl <Shift <Enter
Which will *automatically* enclose the formula in curly brackets,
Which *cannot* be done manually!

Drag down to copy as many rows as you anticipate you might need in order to
return all the possible valid returns.
When you see a #NUM! error, you know that all instances are returned.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"Chris_Hatch" wrote in message
...
Alan,

I get a #NAME? error when I put in your formula. I noticed you used
"vlookups" instead of "vlookup" and you removed the "false" qualifier. Is
that part of the solution?

When I do =vlookup(Jim,range,2,false) it returns Dell. I want to the next
time I put a vlookup command to return Toshiba and then Lenovo respectively.
The way I have it now just keeps returning HP. How do I tell it to go to
the
next Jim's column two value?

"Alan Beban" wrote:

Chris_Hatch wrote:
I want to return the 2nd field for Jim in a single list:

Jim Dell
Jeff HP
Carl Compaq
Jim Toshiba
Carl Sony
Jim Lenovo
Jeff IBM

I want to pull Jim's accounts to another worksheet:

HP
Toshiba
Lenovo

When I use vlookup, it just returns HP and stops. How do I tell it to
return the next instance of Jim?

If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook you can
array enter

=VLookups("Jim",dataRange,2)

Alan Beban

Hi,

Here are some options:

1. I do this only once:
First Mark your area incl. Headlines.
Menu: Data/Filter/AutoFilter
Klick on the small Arrow and to select Tom

2. I do this regularly:
Mark your area incl. the Headlines (include a few extra rows, for the future)
Menu: Data/Pivottable and Chart reports
Drop Names at the top, Computers as Row Item AND as Data Item.
Select Tom

3. I Want a formula:
Make a list on Sheet2
A1: 1
A2: 2
....
B1: =VLOOKUP(A1,Sheet1!A1:B100;2;0)
B2: =... (copy of the above)...
....

Ola

I downloaded the file, but the formula is still not available to me. I've
never done that before though...is there some special command I need to
perform to "load" those formulas into XL?

"tjtjjtjt" wrote:

You have to download from the link Alan provided to use the Vlookups
Function--it's not standard in Excel.

tj

"Chris_Hatch" wrote:

Alan,

I get a #NAME? error when I put in your formula. I noticed you used
"vlookups" instead of "vlookup" and you removed the "false" qualifier. Is
that part of the solution?

When I do =vlookup(Jim,range,2,false) it returns Dell. I want to the next
time I put a vlookup command to return Toshiba and then Lenovo respectively.
The way I have it now just keeps returning HP. How do I tell it to go to the
next Jim's column two value?

"Alan Beban" wrote:

Chris_Hatch wrote:
I want to return the 2nd field for Jim in a single list:

Jim Dell
Jeff HP
Carl Compaq
Jim Toshiba
Carl Sony
Jim Lenovo
Jeff IBM

I want to pull Jim's accounts to another worksheet:

HP
Toshiba
Lenovo

When I use vlookup, it just returns HP and stops. How do I tell it to
return the next instance of Jim?

If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook you can
array enter

=VLookups("Jim",dataRange,2)

Alan Beban

I've never used the <Ctrl <Shift <Enter before. Is that something I press
once, then enter, or enter the data, then press it? Either way I can't seem
to get it to add the curly parentheticals...

"RagDyer" wrote:

You could try this formula using resident XL functions:

Names in Column A
Companies in Column B

Name to lookup in C1

Enter this array formula in D1:

=INDEX($B$1:$B$30,SMALL(IF($A$1:$A$30=$C$1,ROW($A$ 1:$A$30)),ROW(A1)))

Must be entered using <Ctrl <Shift <Enter
Which will *automatically* enclose the formula in curly brackets,
Which *cannot* be done manually!

Drag down to copy as many rows as you anticipate you might need in order to
return all the possible valid returns.
When you see a #NUM! error, you know that all instances are returned.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"Chris_Hatch" wrote in message
...
Alan,

I get a #NAME? error when I put in your formula. I noticed you used
"vlookups" instead of "vlookup" and you removed the "false" qualifier. Is
that part of the solution?

When I do =vlookup(Jim,range,2,false) it returns Dell. I want to the next
time I put a vlookup command to return Toshiba and then Lenovo respectively.
The way I have it now just keeps returning HP. How do I tell it to go to
the
next Jim's column two value?

"Alan Beban" wrote:

Chris_Hatch wrote:
I want to return the 2nd field for Jim in a single list:

Jim Dell
Jeff HP
Carl Compaq
Jim Toshiba
Carl Sony
Jim Lenovo
Jeff IBM

I want to pull Jim's accounts to another worksheet:

HP
Toshiba
Lenovo

When I use vlookup, it just returns HP and stops. How do I tell it to
return the next instance of Jim?

If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook you can
array enter

=VLookups("Jim",dataRange,2)

Alan Beban

You should save the file as an Excel Add-In. Then in your working
workbook in the VB Editor click on Tools|References and check the
downloaded file. Then the functions in it will be available to that
workbook pretty much as though they were built-in functions.

Alan Beban

Chris_Hatch wrote:
I downloaded the file, but the formula is still not available to me. I've
never done that before though...is there some special command I need to
perform to "load" those formulas into XL?

"tjtjjtjt" wrote:


You have to download from the link Alan provided to use the Vlookups
Function--it's not standard in Excel.

tj

"Chris_Hatch" wrote:


Alan,

I get a #NAME? error when I put in your formula. I noticed you used
"vlookups" instead of "vlookup" and you removed the "false" qualifier. Is
that part of the solution?

When I do =vlookup(Jim,range,2,false) it returns Dell. I want to the next
time I put a vlookup command to return Toshiba and then Lenovo respectively.
The way I have it now just keeps returning HP. How do I tell it to go to the
next Jim's column two value?

"Alan Beban" wrote:


Chris_Hatch wrote:

I want to return the 2nd field for Jim in a single list:

Jim Dell
Jeff HP
Carl Compaq
Jim Toshiba
Carl Sony
Jim Lenovo
Jeff IBM

I want to pull Jim's accounts to another worksheet:

HP
Toshiba
Lenovo

When I use vlookup, it just returns HP and stops. How do I tell it to
return the next instance of Jim?


If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook you can
array enter

=VLookups("Jim",dataRange,2)

Alan Beban

After you enter the formula in D1, click in the formula bar or hit <F2
THEN, hold down <Ctrl and <Shift, and then hit <Enter.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

"Chris_Hatch" wrote in message
...
I've never used the <Ctrl <Shift <Enter before. Is that something I
press
once, then enter, or enter the data, then press it? Either way I can't seem
to get it to add the curly parentheticals...

"RagDyer" wrote:

You could try this formula using resident XL functions:

Names in Column A
Companies in Column B

Name to lookup in C1

Enter this array formula in D1:

=INDEX($B$1:$B$30,SMALL(IF($A$1:$A$30=$C$1,ROW($A$ 1:$A$30)),ROW(A1)))

Must be entered using <Ctrl <Shift <Enter
Which will *automatically* enclose the formula in curly brackets,
Which *cannot* be done manually!

Drag down to copy as many rows as you anticipate you might need in order
to
return all the possible valid returns.
When you see a #NUM! error, you know that all instances are returned.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"Chris_Hatch" wrote in message
...
Alan,

I get a #NAME? error when I put in your formula. I noticed you used
"vlookups" instead of "vlookup" and you removed the "false" qualifier. Is
that part of the solution?

When I do =vlookup(Jim,range,2,false) it returns Dell. I want to the next
time I put a vlookup command to return Toshiba and then Lenovo
respectively.
The way I have it now just keeps returning HP. How do I tell it to go to
the
next Jim's column two value?

"Alan Beban" wrote:

Chris_Hatch wrote:
I want to return the 2nd field for Jim in a single list:

Jim Dell
Jeff HP
Carl Compaq
Jim Toshiba
Carl Sony
Jim Lenovo
Jeff IBM

I want to pull Jim's accounts to another worksheet:

HP
Toshiba
Lenovo

When I use vlookup, it just returns HP and stops. How do I tell it to
return the next instance of Jim?

If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook you can
array enter

=VLookups("Jim",dataRange,2)

Alan Beban

RD, You're a genius! Thanks!
BTW, is there any trick to downloading Alan's file? I can't seem to get it
into my XL...

"RagDyer" wrote:

After you enter the formula in D1, click in the formula bar or hit <F2
THEN, hold down <Ctrl and <Shift, and then hit <Enter.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

"Chris_Hatch" wrote in message
...
I've never used the <Ctrl <Shift <Enter before. Is that something I
press
once, then enter, or enter the data, then press it? Either way I can't seem
to get it to add the curly parentheticals...

"RagDyer" wrote:

You could try this formula using resident XL functions:

Names in Column A
Companies in Column B

Name to lookup in C1

Enter this array formula in D1:

=INDEX($B$1:$B$30,SMALL(IF($A$1:$A$30=$C$1,ROW($A$ 1:$A$30)),ROW(A1)))

Must be entered using <Ctrl <Shift <Enter
Which will *automatically* enclose the formula in curly brackets,
Which *cannot* be done manually!

Drag down to copy as many rows as you anticipate you might need in order
to
return all the possible valid returns.
When you see a #NUM! error, you know that all instances are returned.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"Chris_Hatch" wrote in message
...
Alan,

I get a #NAME? error when I put in your formula. I noticed you used
"vlookups" instead of "vlookup" and you removed the "false" qualifier. Is
that part of the solution?

When I do =vlookup(Jim,range,2,false) it returns Dell. I want to the next
time I put a vlookup command to return Toshiba and then Lenovo
respectively.
The way I have it now just keeps returning HP. How do I tell it to go to
the
next Jim's column two value?

"Alan Beban" wrote:

Chris_Hatch wrote:
I want to return the 2nd field for Jim in a single list:

Jim Dell
Jeff HP
Carl Compaq
Jim Toshiba
Carl Sony
Jim Lenovo
Jeff IBM

I want to pull Jim's accounts to another worksheet:

HP
Toshiba
Lenovo

When I use vlookup, it just returns HP and stops. How do I tell it to
return the next instance of Jim?

If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook you can
array enter

=VLookups("Jim",dataRange,2)

Alan Beban

I'm trying to do the same thing, but needs the results in a separate
workbook. I could use a little help on the syntax, so that I get the right
fields. I'm looking at using column A on sheet 2 for the value that I'm
looking for, which is in column H of sheet 1, and I need to return columns F
through O from sheet 1 onto sheet 2. I would appreciate any help that can be
provided.

Thanks!!

"Chris_Hatch" wrote:

RD, You're a genius! Thanks!
BTW, is there any trick to downloading Alan's file? I can't seem to get it
into my XL...

"RagDyer" wrote:

After you enter the formula in D1, click in the formula bar or hit <F2
THEN, hold down <Ctrl and <Shift, and then hit <Enter.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

"Chris_Hatch" wrote in message
...
I've never used the <Ctrl <Shift <Enter before. Is that something I
press
once, then enter, or enter the data, then press it? Either way I can't seem
to get it to add the curly parentheticals...

"RagDyer" wrote:

You could try this formula using resident XL functions:

Names in Column A
Companies in Column B

Name to lookup in C1

Enter this array formula in D1:

=INDEX($B$1:$B$30,SMALL(IF($A$1:$A$30=$C$1,ROW($A$ 1:$A$30)),ROW(A1)))

Must be entered using <Ctrl <Shift <Enter
Which will *automatically* enclose the formula in curly brackets,
Which *cannot* be done manually!

Drag down to copy as many rows as you anticipate you might need in order
to
return all the possible valid returns.
When you see a #NUM! error, you know that all instances are returned.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"Chris_Hatch" wrote in message
...
Alan,

I get a #NAME? error when I put in your formula. I noticed you used
"vlookups" instead of "vlookup" and you removed the "false" qualifier. Is
that part of the solution?

When I do =vlookup(Jim,range,2,false) it returns Dell. I want to the next
time I put a vlookup command to return Toshiba and then Lenovo
respectively.
The way I have it now just keeps returning HP. How do I tell it to go to
the
next Jim's column two value?

"Alan Beban" wrote:

Chris_Hatch wrote:
I want to return the 2nd field for Jim in a single list:

Jim Dell
Jeff HP
Carl Compaq
Jim Toshiba
Carl Sony
Jim Lenovo
Jeff IBM

I want to pull Jim's accounts to another worksheet:

HP
Toshiba
Lenovo

When I use vlookup, it just returns HP and stops. How do I tell it to
return the next instance of Jim?

If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook you can
array enter

=VLookups("Jim",dataRange,2)

Alan Beban

vlookup for same name, multiple instances...
 

I have downloaded the array functions...and see the functions in the VB
Editor. But the functions aren't working in the spreadsheet. Is there
something futher I need to do for this to work?? Thanks!

"Alan Beban" wrote:

You should save the file as an Excel Add-In. Then in your working
workbook in the VB Editor click on Tools|References and check the
downloaded file. Then the functions in it will be available to that
workbook pretty much as though they were built-in functions.

Alan Beban

Chris_Hatch wrote:
I downloaded the file, but the formula is still not available to me. I've
never done that before though...is there some special command I need to
perform to "load" those formulas into XL?

"tjtjjtjt" wrote:


You have to download from the link Alan provided to use the Vlookups
Function--it's not standard in Excel.

tj

"Chris_Hatch" wrote:


Alan,

I get a #NAME? error when I put in your formula. I noticed you used
"vlookups" instead of "vlookup" and you removed the "false" qualifier. Is
that part of the solution?

When I do =vlookup(Jim,range,2,false) it returns Dell. I want to the next
time I put a vlookup command to return Toshiba and then Lenovo respectively.
The way I have it now just keeps returning HP. How do I tell it to go to the
next Jim's column two value?

"Alan Beban" wrote:


Chris_Hatch wrote:

I want to return the 2nd field for Jim in a single list:

Jim Dell
Jeff HP
Carl Compaq
Jim Toshiba
Carl Sony
Jim Lenovo
Jeff IBM

I want to pull Jim's accounts to another worksheet:

HP
Toshiba
Lenovo

When I use vlookup, it just returns HP and stops. How do I tell it to
return the next instance of Jim?


If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook you can
array enter

=VLookups("Jim",dataRange,2)

Alan Beban

vlookup for same name, multiple instances...
 

liseladele wrote:
I have downloaded the array functions...and see the functions in the VB
Editor. But the functions aren't working in the spreadsheet. Is there
something futher I need to do for this to work?? Thanks!


In the workbook of the spreadsheet in which you want the functions to
work, Click on Tools, VBEditor, Tools, References, and check the
reference to the file in which the functions reside. Post back if that
doesn't solve the problem.

Alan Beban

vlookup for same name, multiple instances...
 

I guess I'm unsure of which reference is the one to check...any help??
Thanks!!

"Alan Beban" wrote:

liseladele wrote:
I have downloaded the array functions...and see the functions in the VB
Editor. But the functions aren't working in the spreadsheet. Is there
something futher I need to do for this to work?? Thanks!


In the workbook of the spreadsheet in which you want the functions to
work, Click on Tools, VBEditor, Tools, References, and check the
reference to the file in which the functions reside. Post back if that
doesn't solve the problem.

Alan Beban

vlookup for same name, multiple instances...
 

hm....now it's saying...'must select 15 rows'...any ideas?? Thanks!

"Alan Beban" wrote:

liseladele wrote:
I have downloaded the array functions...and see the functions in the VB
Editor. But the functions aren't working in the spreadsheet. Is there
something futher I need to do for this to work?? Thanks!


In the workbook of the spreadsheet in which you want the functions to
work, Click on Tools, VBEditor, Tools, References, and check the
reference to the file in which the functions reside. Post back if that
doesn't solve the problem.

Alan Beban

vlookup for same name, multiple instances...
 

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!
"cursednomore" wrote:

I'm trying to do the same thing, but needs the results in a separate
workbook. I could use a little help on the syntax, so that I get the right
fields. I'm looking at using column A on sheet 2 for the value that I'm
looking for, which is in column H of sheet 1, and I need to return columns F
through O from sheet 1 onto sheet 2. I would appreciate any help that can be
provided.

Thanks!!

"Chris_Hatch" wrote:

RD, You're a genius! Thanks!
BTW, is there any trick to downloading Alan's file? I can't seem to get it
into my XL...

"RagDyer" wrote:

After you enter the formula in D1, click in the formula bar or hit <F2
THEN, hold down <Ctrl and <Shift, and then hit <Enter.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

"Chris_Hatch" wrote in message
...
I've never used the <Ctrl <Shift <Enter before. Is that something I
press
once, then enter, or enter the data, then press it? Either way I can't seem
to get it to add the curly parentheticals...

"RagDyer" wrote:

You could try this formula using resident XL functions:

Names in Column A
Companies in Column B

Name to lookup in C1

Enter this array formula in D1:

=INDEX($B$1:$B$30,SMALL(IF($A$1:$A$30=$C$1,ROW($A$ 1:$A$30)),ROW(A1)))

Must be entered using <Ctrl <Shift <Enter
Which will *automatically* enclose the formula in curly brackets,
Which *cannot* be done manually!

Drag down to copy as many rows as you anticipate you might need in order
to
return all the possible valid returns.
When you see a #NUM! error, you know that all instances are returned.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"Chris_Hatch" wrote in message
...
Alan,

I get a #NAME? error when I put in your formula. I noticed you used
"vlookups" instead of "vlookup" and you removed the "false" qualifier. Is
that part of the solution?

When I do =vlookup(Jim,range,2,false) it returns Dell. I want to the next
time I put a vlookup command to return Toshiba and then Lenovo
respectively.
The way I have it now just keeps returning HP. How do I tell it to go to
the
next Jim's column two value?

"Alan Beban" wrote:

Chris_Hatch wrote:
I want to return the 2nd field for Jim in a single list:

Jim Dell
Jeff HP
Carl Compaq
Jim Toshiba
Carl Sony
Jim Lenovo
Jeff IBM

I want to pull Jim's accounts to another worksheet:

HP
Toshiba
Lenovo

When I use vlookup, it just returns HP and stops. How do I tell it to
return the next instance of Jim?

If the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook you can
array enter

=VLookups("Jim",dataRange,2)

Alan Beban

vlookup for same name, multiple instances...
 

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!

vlookup for same name, multiple instances...
 

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!

vlookup for same name, multiple instances...
 

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 -