vlookup for same name, multiple instances...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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 said:
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?
Click to expand...
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?
 
You have to download from the link Alan provided to use the Vlookups
Function--it's not standard in Excel.

tj
 
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!
==============================================


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?
 
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?
 
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...
 
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
 
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!
==============================================

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...
 
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!!
 
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!
 
liseladele said:
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!
Click to expand...

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
 
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!
 
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
 
Hi Pete,

yes, exactly!

Pete_UK said:
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
Click to expand...
 
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
 
Back
Top