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.


--
GarToms

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
>

http://office.microsoft.com/en-us/as...0corresponding

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 [email protected]

Thanks
Gavin

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

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...0corresponding
>
> 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
> [email protected]
>
> 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
>
>

>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." <[email protected]> wrote in message
news:[email protected]...
> 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...0corresponding
>>
>> 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
>> [email protected]
>>
>> 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
>>
>>

What are trying to do?

Biff

"Gavin1969" <[email protected]> wrote
in message news:[email protected]...
>
> http://office.microsoft.com/en-us/as...0corresponding
>
> 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
> [email protected]
>
> 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
>

Bookmark

"GarToms" <[email protected]> wrote in message
news:[email protected]...
>
> 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

Take a look at this sample file:

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

Biff

"GarToms" <[email protected]> wrote in message
news:[email protected]...
>
> 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

Take a look at this sample file:

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

Biff

"Gavin1969" <[email protected]> wrote
in message news:[email protected]...
>
> http://office.microsoft.com/en-us/as...0corresponding
>
> 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
> [email protected]
>
> 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
>

Thanks

That is exactly what i needed to know.


Biff Wrote:
> Take a look at this sample file:
>
> http://s19.yousendit.com/d.aspx?id=1...03CJM84T9Z306Y
>
> Biff
>
> "GarToms" [email protected] wrote in message
> news:[email protected]...
>
> 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


--
GarToms

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$20=$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

Hi!

> Currently a table of data is transfered to a sheet using
> (INDEX(sheet1!$C$2:$C$20,SMALL(IF(sheet1!$A$2:$A$20=$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" <[email protected]> wrote in message
news:[email protected]...
>
> 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$20=$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

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