Want Vlookup to list multiple items with the same key?

G

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.
 
C

CLR

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

Vaya con Dios,
Chuck, CABGx3
 
G

GarToms

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)))
 
J

John M.

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
 
B

Biff

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

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
 
G

GarToms

Thanks

That is exactly what i needed to know.

Take a look at this sample file:

http://s19.yousendit.com/d.aspx?id=11C7VN3PMW9J03CJM84T9Z306Y

Biff

"GarToms" [email protected] 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.
Click to expand...
 
G

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.
 
B

Biff

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))
Click to expand...

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
 
Top