software

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: ____
KeyName
1414BRODRENE DAHL A/S
1880MAN FERROSTAAL AG
2356HEITON BUCKLEY LIMITED
2356HEITON BUCKLEY LIMITED1
2356HEITON BUCKLEY LIMITED2
2356HEITON BUCKLEY LIMITED3
3867STAVANGER RORHANDEL A/S
4367CLEANAWAY LTD
4618ALUKONIGSTAHL GMBH
4618ALUKONIGSTAHL GMBH1
4979MARMON/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.
You might take a look at Data gt; Filter gt; AutoFilter.........it does something
similar to what you describe.

Vaya con Dios,
Chuck, CABGx3quot;GarTomsquot; wrote:

gt;
gt; I want to do a function similar to a vlookup. I want to input a key
gt; into a cell and have the relevant names listed. The problem I have is
gt; the vlookup only shows one of the multiple names. Is there a way to
gt; list all of the names?
gt;
gt; Input Key: ____
gt;
gt; KeyName
gt; 1414BRODRENE DAHL A/S
gt; 1880MAN FERROSTAAL AG
gt; 2356HEITON BUCKLEY LIMITED
gt; 2356HEITON BUCKLEY LIMITED1
gt; 2356HEITON BUCKLEY LIMITED2
gt; 2356HEITON BUCKLEY LIMITED3
gt; 3867STAVANGER RORHANDEL A/S
gt; 4367CLEANAWAY LTD
gt; 4618ALUKONIGSTAHL GMBH
gt; 4618ALUKONIGSTAHL GMBH1
gt; 4979MARMON/KEYSTONE ANBUMA N.V.
gt;
gt;
gt; Ideal Output
gt;
gt; Input Key 2356
gt;
gt; HEITON BUCKLEY LIMITED //all brought up by a formula
gt; HEITON BUCKLEY LIMITED1
gt; HEITON BUCKLEY LIMITED2
gt; HEITON BUCKLEY LIMITED3
gt;
gt; I would appreciate any assistance.
gt; Thanks.
gt;
gt;
gt; --
gt; GarToms
gt;


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 amp; Paste the whole of the address into your address bar.

If you get this to work, please can you email it to meThanks
Gavin--
Gavin1969
------------------------------------------------------------------------
Gavin1969's Profile: www.excelforum.com/member.php...oamp;userid=30551
View this thread: www.excelforum.com/showthread...hreadid=502001I 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)))
Originally Posted by GarTomsI 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: ____
KeyName
1414BRODRENE DAHL A/S
1880MAN FERROSTAAL AG
2356HEITON BUCKLEY LIMITED
2356HEITON BUCKLEY LIMITED1
2356HEITON BUCKLEY LIMITED2
2356HEITON BUCKLEY LIMITED3
3867STAVANGER RORHANDEL A/S
4367CLEANAWAY LTD
4618ALUKONIGSTAHL GMBH
4618ALUKONIGSTAHL GMBH1
4979MARMON/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.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)),quot;quot;,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

quot;Gavin1969quot; wrote:

gt;
gt; office.microsoft.com/en-us/as...orrespond ing
gt;
gt; This is what you are after, but I have spent many frustrating hours
gt; trying to get it to work, I am still desperate for this result myself.
gt;
gt; Copy amp; Paste the whole of the address into your address bar.
gt;
gt; If you get this to work, please can you email it to me
gt;
gt;
gt; Thanks
gt; Gavin
gt;
gt;
gt; --
gt; Gavin1969
gt; ------------------------------------------------------------------------
gt; Gavin1969's Profile: www.excelforum.com/member.php...oamp;userid=30551
gt; View this thread: www.excelforum.com/showthread...hreadid=502001
gt;
gt;

gt;Without passing judgement on whether this is the best way to meet your end
gt;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

quot;John M.quot; gt; wrote in message
...
gt; Without passing judgement on whether this is the best way to meet your end
gt; objective (vs. using a pivottable or simple auto-filter)...
gt;
gt; The formula referenced in the article below works fine. Since the result
gt; set can be an array up to the same size as the list, you will need to copy
gt; the formula into the same number of rows as the list (i.e. if you have 500
gt; rows in your list, the formula should reside in 500 rows otherwise you may
gt; truncate your result).
gt;
gt; Place the formula in the first row of where you want your resultant set
gt; (remembering to use shift ctrl enter since it is an array formula) and
gt; then
gt; autofill the formula into the remaining rows for the result set. This
gt; will
gt; ensure that the Row(1:1) is incremented to Row(2:2) etc. on subsequent
gt; rows.
gt;
gt; Replace $A$1:$B$7 with the data range; replace $A$1:$A$7 with the range
gt; that
gt; you are testing; replace $A$10 with the reference to the cell that has the
gt; value you are testing for.
gt;
gt; =IF(ISERROR(INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$ 10,ROW($A$1:$A$7)),ROW(1:1)),2)),quot;quot;,INDEX($A$1:$B$ 7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1 )),2))
gt;
gt; Good luck!
gt;
gt; John
gt;
gt; Top of Page
gt;
gt;
gt;
gt;
gt; quot;Gavin1969quot; wrote:
gt;
gt;gt;
gt;gt; office.microsoft.com/en-us/as...orrespond ing
gt;gt;
gt;gt; This is what you are after, but I have spent many frustrating hours
gt;gt; trying to get it to work, I am still desperate for this result myself.
gt;gt;
gt;gt; Copy amp; Paste the whole of the address into your address bar.
gt;gt;
gt;gt; If you get this to work, please can you email it to me
gt;gt;
gt;gt;
gt;gt; Thanks
gt;gt; Gavin
gt;gt;
gt;gt;
gt;gt; --
gt;gt; Gavin1969
gt;gt; ------------------------------------------------------------------------
gt;gt; Gavin1969's Profile:
gt;gt; www.excelforum.com/member.php...oamp;userid=30551
gt;gt; View this thread:
gt;gt; www.excelforum.com/showthread...hreadid=502001
gt;gt;
gt;gt;
What are trying to do?

Biff

quot;Gavin1969quot; gt; wrote
in message ...
gt;
gt; office.microsoft.com/en-us/as...orrespond ing
gt;
gt; This is what you are after, but I have spent many frustrating hours
gt; trying to get it to work, I am still desperate for this result myself.
gt;
gt; Copy amp; Paste the whole of the address into your address bar.
gt;
gt; If you get this to work, please can you email it to me
gt;
gt;
gt; Thanks
gt; Gavin
gt;
gt;
gt; --
gt; Gavin1969
gt; ------------------------------------------------------------------------
gt; Gavin1969's Profile:
gt; www.excelforum.com/member.php...oamp;userid=30551
gt; View this thread: www.excelforum.com/showthread...hreadid=502001
gt;
Bookmark

quot;GarTomsquot; gt; wrote in message
...
gt;
gt; I want to do a function similar to a vlookup. I want to input a key
gt; into a cell and have the relevant names listed. The problem I have is
gt; the vlookup only shows one of the multiple names. Is there a way to
gt; list all of the names?
gt;
gt; Input Key: ____
gt;
gt; Key Name
gt; 1414 BRODRENE DAHL A/S
gt; 1880 MAN FERROSTAAL AG
gt; 2356 HEITON BUCKLEY LIMITED
gt; 2356 HEITON BUCKLEY LIMITED1
gt; 2356 HEITON BUCKLEY LIMITED2
gt; 2356 HEITON BUCKLEY LIMITED3
gt; 3867 STAVANGER RORHANDEL A/S
gt; 4367 CLEANAWAY LTD
gt; 4618 ALUKONIGSTAHL GMBH
gt; 4618 ALUKONIGSTAHL GMBH1
gt; 4979 MARMON/KEYSTONE ANBUMA N.V.
gt;
gt;
gt; Ideal Output
gt;
gt; Input Key 2356
gt;
gt; HEITON BUCKLEY LIMITED //all brought up by a formula
gt; HEITON BUCKLEY LIMITED1
gt; HEITON BUCKLEY LIMITED2
gt; HEITON BUCKLEY LIMITED3
gt;
gt; I would appreciate any assistance.
gt; Thanks.
gt;
gt;
gt; --
gt; GarToms
Take a look at this sample file:

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

Biff

quot;Gavin1969quot; gt; wrote
in message ...
gt;
gt; office.microsoft.com/en-us/as...orrespond ing
gt;
gt; This is what you are after, but I have spent many frustrating hours
gt; trying to get it to work, I am still desperate for this result myself.
gt;
gt; Copy amp; Paste the whole of the address into your address bar.
gt;
gt; If you get this to work, please can you email it to me
gt;
gt;
gt; Thanks
gt; Gavin
gt;
gt;
gt; --
gt; Gavin1969
gt; ------------------------------------------------------------------------
gt; Gavin1969's Profile:
gt; www.excelforum.com/member.php...oamp;userid=30551
gt; View this thread: www.excelforum.com/showthread...hreadid=502001
gt;
Take a look at this sample file:

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

Biff

quot;GarTomsquot; gt; wrote in message
...
gt;
gt; I have found this formula that does a similar function to what I require
gt; however I am unable to edit it to A1:C1000. Does anyone know how to
gt; amend this to work?
gt;
gt; =INDEX(Sheet1!B$2:B$4,SMALL(IF(Sheet1!$A$2:$A$4=$A
gt; $1,ROW($1:$3)),ROW(1:1)))
gt;
gt;
gt; GarToms Wrote:
gt;gt; I want to do a function similar to a vlookup. I want to input a key
gt;gt; into a cell and have the relevant names listed. The problem I have is
gt;gt; the vlookup only shows one of the multiple names. Is there a way to
gt;gt; list all of the names?
gt;gt;
gt;gt; Input Key: ____
gt;gt;
gt;gt; Key Name
gt;gt; 1414 BRODRENE DAHL A/S
gt;gt; 1880 MAN FERROSTAAL AG
gt;gt; 2356 HEITON BUCKLEY LIMITED
gt;gt; 2356 HEITON BUCKLEY LIMITED1
gt;gt; 2356 HEITON BUCKLEY LIMITED2
gt;gt; 2356 HEITON BUCKLEY LIMITED3
gt;gt; 3867 STAVANGER RORHANDEL A/S
gt;gt; 4367 CLEANAWAY LTD
gt;gt; 4618 ALUKONIGSTAHL GMBH
gt;gt; 4618 ALUKONIGSTAHL GMBH1
gt;gt; 4979 MARMON/KEYSTONE ANBUMA N.V.
gt;gt;
gt;gt;
gt;gt; Ideal Output
gt;gt;
gt;gt; Input Key 2356
gt;gt;
gt;gt; HEITON BUCKLEY LIMITED //all brought up by a formula
gt;gt; HEITON BUCKLEY LIMITED1
gt;gt; HEITON BUCKLEY LIMITED2
gt;gt; HEITON BUCKLEY LIMITED3
gt;gt;
gt;gt; I would appreciate any assistance.
gt;gt; Thanks.
gt;
gt;
gt; --
gt; GarToms