Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Member
 
Posts: 33
Default Want Vlookup to list multiple items with the same key?

I want to do a function similar to a vlookup. I want to input a key into a cell and have the relevant names listed. The problem I have is the vlookup only shows one of the multiple names. Is there a way to list all of the names?

Input Key: ____

Key Name
1414 BRODRENE DAHL A/S
1880 MAN FERROSTAAL AG
2356 HEITON BUCKLEY LIMITED
2356 HEITON BUCKLEY LIMITED1
2356 HEITON BUCKLEY LIMITED2
2356 HEITON BUCKLEY LIMITED3
3867 STAVANGER RORHANDEL A/S
4367 CLEANAWAY LTD
4618 ALUKONIGSTAHL GMBH
4618 ALUKONIGSTAHL GMBH1
4979 MARMON/KEYSTONE ANBUMA N.V.


Ideal Output

Input Key 2356

HEITON BUCKLEY LIMITED //all brought up by a formula
HEITON BUCKLEY LIMITED1
HEITON BUCKLEY LIMITED2
HEITON BUCKLEY LIMITED3

I would appreciate any assistance.
Thanks.
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
CLR
 
Posts: n/a
Default Want Vlookup to list multiple items with the same key?

You might take a look at Data Filter AutoFilter.........it does something
similar to what you describe.

Vaya con Dios,
Chuck, CABGx3





"GarToms" wrote:


I want to do a function similar to a vlookup. I want to input a key
into a cell and have the relevant names listed. The problem I have is
the vlookup only shows one of the multiple names. Is there a way to
list all of the names?

Input Key: ____

Key Name
1414 BRODRENE DAHL A/S
1880 MAN FERROSTAAL AG
2356 HEITON BUCKLEY LIMITED
2356 HEITON BUCKLEY LIMITED1
2356 HEITON BUCKLEY LIMITED2
2356 HEITON BUCKLEY LIMITED3
3867 STAVANGER RORHANDEL A/S
4367 CLEANAWAY LTD
4618 ALUKONIGSTAHL GMBH
4618 ALUKONIGSTAHL GMBH1
4979 MARMON/KEYSTONE ANBUMA N.V.


Ideal Output

Input Key 2356

HEITON BUCKLEY LIMITED //all brought up by a formula
HEITON BUCKLEY LIMITED1
HEITON BUCKLEY LIMITED2
HEITON BUCKLEY LIMITED3

I would appreciate any assistance.
Thanks.


--
GarToms

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Gavin1969
 
Posts: n/a
Default Want Vlookup to list multiple items with the same key?


http://office.microsoft.com/en-us/as...orrespond ing

This is what you are after, but I have spent many frustrating hours
trying to get it to work, I am still desperate for this result myself.

Copy & Paste the whole of the address into your address bar.

If you get this to work, please can you email it to me


Thanks
Gavin


--
Gavin1969
------------------------------------------------------------------------
Gavin1969's Profile:
http://www.excelforum.com/member.php...o&userid=30551
View this thread: http://www.excelforum.com/showthread...hreadid=502001

  #4   Report Post  
Member
 
Posts: 33
Default

I have found this formula that does a similar function to what I require however I am unable to edit it to A1:C1000. Does anyone know how to amend this to work?

=INDEX(Sheet1!B$2:B$4,SMALL(IF(Sheet1!$A$2:$A$4=$A $1,ROW($1:$3)),ROW(1:1)))


Quote:
Originally Posted by GarToms
I want to do a function similar to a vlookup. I want to input a key into a cell and have the relevant names listed. The problem I have is the vlookup only shows one of the multiple names. Is there a way to list all of the names?

Input Key: ____

Key Name
1414 BRODRENE DAHL A/S
1880 MAN FERROSTAAL AG
2356 HEITON BUCKLEY LIMITED
2356 HEITON BUCKLEY LIMITED1
2356 HEITON BUCKLEY LIMITED2
2356 HEITON BUCKLEY LIMITED3
3867 STAVANGER RORHANDEL A/S
4367 CLEANAWAY LTD
4618 ALUKONIGSTAHL GMBH
4618 ALUKONIGSTAHL GMBH1
4979 MARMON/KEYSTONE ANBUMA N.V.


Ideal Output

Input Key 2356

HEITON BUCKLEY LIMITED //all brought up by a formula
HEITON BUCKLEY LIMITED1
HEITON BUCKLEY LIMITED2
HEITON BUCKLEY LIMITED3

I would appreciate any assistance.
Thanks.
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
John M.
 
Posts: n/a
Default Want Vlookup to list multiple items with the same key?

Without passing judgement on whether this is the best way to meet your end
objective (vs. using a pivottable or simple auto-filter)...

The formula referenced in the article below works fine. Since the result
set can be an array up to the same size as the list, you will need to copy
the formula into the same number of rows as the list (i.e. if you have 500
rows in your list, the formula should reside in 500 rows otherwise you may
truncate your result).

Place the formula in the first row of where you want your resultant set
(remembering to use shift+ctrl+enter since it is an array formula) and then
autofill the formula into the remaining rows for the result set. This will
ensure that the Row(1:1) is incremented to Row(2:2) etc. on subsequent rows.

Replace $A$1:$B$7 with the data range; replace $A$1:$A$7 with the range that
you are testing; replace $A$10 with the reference to the cell that has the
value you are testing for.

=IF(ISERROR(INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$ 10,ROW($A$1:$A$7)),ROW(1:1)),2)),"",INDEX($A$1:$B$ 7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1 )),2))

Good luck!

John

Top of Page




"Gavin1969" wrote:


http://office.microsoft.com/en-us/as...orrespond ing

This is what you are after, but I have spent many frustrating hours
trying to get it to work, I am still desperate for this result myself.

Copy & Paste the whole of the address into your address bar.

If you get this to work, please can you email it to me


Thanks
Gavin


--
Gavin1969
------------------------------------------------------------------------
Gavin1969's Profile:
http://www.excelforum.com/member.php...o&userid=30551
View this thread: http://www.excelforum.com/showthread...hreadid=502001




  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Biff
 
Posts: n/a
Default Want Vlookup to list multiple items with the same key?

Without passing judgement on whether this is the best way to meet your end
objective (vs. using a pivottable or simple auto-filter)...


I use these types of formulas every day. This particular formula can be
shortened a little and also made a little more efficient.

The big difference between using these types of formulas versus pivot tables
and filters is that the formula method is dynamic!

Biff

"John M." wrote in message
...
Without passing judgement on whether this is the best way to meet your end
objective (vs. using a pivottable or simple auto-filter)...

The formula referenced in the article below works fine. Since the result
set can be an array up to the same size as the list, you will need to copy
the formula into the same number of rows as the list (i.e. if you have 500
rows in your list, the formula should reside in 500 rows otherwise you may
truncate your result).

Place the formula in the first row of where you want your resultant set
(remembering to use shift+ctrl+enter since it is an array formula) and
then
autofill the formula into the remaining rows for the result set. This
will
ensure that the Row(1:1) is incremented to Row(2:2) etc. on subsequent
rows.

Replace $A$1:$B$7 with the data range; replace $A$1:$A$7 with the range
that
you are testing; replace $A$10 with the reference to the cell that has the
value you are testing for.

=IF(ISERROR(INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$ 10,ROW($A$1:$A$7)),ROW(1:1)),2)),"",INDEX($A$1:$B$ 7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1 )),2))

Good luck!

John

Top of Page




"Gavin1969" wrote:


http://office.microsoft.com/en-us/as...orrespond ing

This is what you are after, but I have spent many frustrating hours
trying to get it to work, I am still desperate for this result myself.

Copy & Paste the whole of the address into your address bar.

If you get this to work, please can you email it to me


Thanks
Gavin


--
Gavin1969
------------------------------------------------------------------------
Gavin1969's Profile:
http://www.excelforum.com/member.php...o&userid=30551
View this thread:
http://www.excelforum.com/showthread...hreadid=502001




  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Biff
 
Posts: n/a
Default Want Vlookup to list multiple items with the same key?

What are trying to do?

Biff

"Gavin1969" wrote
in message ...

http://office.microsoft.com/en-us/as...orrespond ing

This is what you are after, but I have spent many frustrating hours
trying to get it to work, I am still desperate for this result myself.

Copy & Paste the whole of the address into your address bar.

If you get this to work, please can you email it to me


Thanks
Gavin


--
Gavin1969
------------------------------------------------------------------------
Gavin1969's Profile:
http://www.excelforum.com/member.php...o&userid=30551
View this thread: http://www.excelforum.com/showthread...hreadid=502001



  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Biff
 
Posts: n/a
Default Want Vlookup to list multiple items with the same key?

Bookmark

"GarToms" wrote in message
...

I want to do a function similar to a vlookup. I want to input a key
into a cell and have the relevant names listed. The problem I have is
the vlookup only shows one of the multiple names. Is there a way to
list all of the names?

Input Key: ____

Key Name
1414 BRODRENE DAHL A/S
1880 MAN FERROSTAAL AG
2356 HEITON BUCKLEY LIMITED
2356 HEITON BUCKLEY LIMITED1
2356 HEITON BUCKLEY LIMITED2
2356 HEITON BUCKLEY LIMITED3
3867 STAVANGER RORHANDEL A/S
4367 CLEANAWAY LTD
4618 ALUKONIGSTAHL GMBH
4618 ALUKONIGSTAHL GMBH1
4979 MARMON/KEYSTONE ANBUMA N.V.


Ideal Output

Input Key 2356

HEITON BUCKLEY LIMITED //all brought up by a formula
HEITON BUCKLEY LIMITED1
HEITON BUCKLEY LIMITED2
HEITON BUCKLEY LIMITED3

I would appreciate any assistance.
Thanks.


--
GarToms



  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Biff
 
Posts: n/a
Default Want Vlookup to list multiple items with the same key?

Take a look at this sample file:

http://s19.yousendit.com/d.aspx?id=1...03CJM84T9Z306Y

Biff

"Gavin1969" wrote
in message ...

http://office.microsoft.com/en-us/as...orrespond ing

This is what you are after, but I have spent many frustrating hours
trying to get it to work, I am still desperate for this result myself.

Copy & Paste the whole of the address into your address bar.

If you get this to work, please can you email it to me


Thanks
Gavin


--
Gavin1969
------------------------------------------------------------------------
Gavin1969's Profile:
http://www.excelforum.com/member.php...o&userid=30551
View this thread: http://www.excelforum.com/showthread...hreadid=502001



  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Biff
 
Posts: n/a
Default Want Vlookup to list multiple items with the same key?

Take a look at this sample file:

http://s19.yousendit.com/d.aspx?id=1...03CJM84T9Z306Y

Biff

"GarToms" wrote in message
...

I have found this formula that does a similar function to what I require
however I am unable to edit it to A1:C1000. Does anyone know how to
amend this to work?

=INDEX(Sheet1!B$2:B$4,SMALL(IF(Sheet1!$A$2:$A$4=$A
$1,ROW($1:$3)),ROW(1:1)))


GarToms Wrote:
I want to do a function similar to a vlookup. I want to input a key
into a cell and have the relevant names listed. The problem I have is
the vlookup only shows one of the multiple names. Is there a way to
list all of the names?

Input Key: ____

Key Name
1414 BRODRENE DAHL A/S
1880 MAN FERROSTAAL AG
2356 HEITON BUCKLEY LIMITED
2356 HEITON BUCKLEY LIMITED1
2356 HEITON BUCKLEY LIMITED2
2356 HEITON BUCKLEY LIMITED3
3867 STAVANGER RORHANDEL A/S
4367 CLEANAWAY LTD
4618 ALUKONIGSTAHL GMBH
4618 ALUKONIGSTAHL GMBH1
4979 MARMON/KEYSTONE ANBUMA N.V.


Ideal Output

Input Key 2356

HEITON BUCKLEY LIMITED //all brought up by a formula
HEITON BUCKLEY LIMITED1
HEITON BUCKLEY LIMITED2
HEITON BUCKLEY LIMITED3

I would appreciate any assistance.
Thanks.



--
GarToms





  #12   Report Post  
Member
 
Posts: 33
Default

Biff and all,

I wonder if you could assist me further with something using the formula you suggested.

Currently a table of data is transfered to a sheet using
(INDEX(sheet1!$C$2:$C$20,SMALL(IF(sheet1!$A$2:$A$2 0=$C$2,ROW($1:$20)),ROW(1:1))

The rows search the source data in sheet 1 by a key (column A) and display a name (column B), a value (column C) and a digit in column D decides under where the value (column C) will go. The column C value needs to go under column E, F, G, H, I, and to make it more complecated these column headings are formulas and change when the data is updated.

The table transfers all the data but i cannot make it sort the value in column C to be in the correct column in my table.

Anyone have any ideas? I was thinking there may be an if statement or something i could use.

I would be very great full for any assistance with this.
  #13   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Biff
 
Posts: n/a
Default Want Vlookup to list multiple items with the same key?

Hi!

Currently a table of data is transfered to a sheet using
(INDEX(sheet1!$C$2:$C$20,SMALL(IF(sheet1!$A$2:$A$2 0=$C$2,ROW($1:$20)),ROW(1:1))


As written that formula will not work properly. You'll either get errors or
possibly incorrect results.

The size of the array that is indexed: sheet1!$C$2:$C$20

Must be the same size as: ROW($1:$20)

sheet1!$C$2:$C$20 = 19
ROW($1:$20) = 20

The "least" confusing way to make sure these arrays are the same size is to
use the same sized range reference in the ROW function as you do the INDEX
and then subtract the offset:

ROW(C$2:C$20)-ROW(C$2)+1

The purpose of the expression: ROW(C$2:C$20)-ROW(C$2)+1

is to return an array that is the same size as the indexed array

So:

sheet1!$C$2:$C$20 = 19 (1:19)
ROW(C$2:C$20)-ROW(C$2)+1 = 19 (1:19)

Ok, now, as far as your latest question goes, I'm not following you!!!!

I don't understand what you're trying to do!

Biff

"GarToms" wrote in message
...

Biff and all,

I wonder if you could assist me further with something using the
formula you suggested.

Currently a table of data is transfered to a sheet using
(INDEX(sheet1!$C$2:$C$20,SMALL(IF(sheet1!$A$2:$A$2 0=$C$2,ROW($1:$20)),ROW(1:1))

The rows search the source data in sheet 1 by a key (column A) and
display a name (column B), a value (column C) and a digit in column D
decides under where the value (column C) will go. The column C value
needs to go under column E, F, G, H, I, and to make it more complecated
these column headings are formulas and change when the data is updated.

The table transfers all the data but i cannot make it sort the value in
column C to be in the correct column in my table.

Anyone have any ideas? I was thinking there may be an if statement or
something i could use.

I would be very great full for any assistance with this.


--
GarToms



  #14   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Dark_Templar
 
Posts: n/a
Default Want Vlookup to list multiple items with the same key?


Is there any way to solve this with a pivot table?


--
Dark_Templar
------------------------------------------------------------------------
Dark_Templar's Profile: http://www.excelforum.com/member.php...o&userid=33279
View this thread: http://www.excelforum.com/showthread...hreadid=502001

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
why won't vlookup work in a long list pkeegs Excel Worksheet Functions 9 August 2nd 05 02:28 PM
how do I use vlookup for multiple occurrences of the same value bj Excel Worksheet Functions 0 April 27th 05 10:43 PM
Items in a List jcliquidtension Excel Discussion (Misc queries) 1 April 5th 05 10:23 PM
Transfer Items to a list with no duplicates [email protected] Excel Worksheet Functions 1 March 30th 05 10:30 PM
Can VLOOKUP return multiple answers based on several identical lo. jddtct Excel Worksheet Functions 3 January 11th 05 08:03 AM


All times are GMT +1. The time now is 04:33 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"