vlookup to return multiple lines

[Christopher]

hi,
i'm using a vlookup to find data which is in an array
mutliple times. The problem is that the vlookup only
returns the first occurance of the data i'm looking for.
Does anybody know of an alternative formula that will give
multiple returns?

thank you
chris

 

[Frank Kabel]

Hi
you may try downloadind Alan Beban's array functions
(http://home.pacbell.net/beban)
They'll include a function called VLOOKUPS (for returning multiple
lookup results)

 

[RagDyer]

See if this old post addresses your question:

http://tinyurl.com/2x8k5
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================
hi,
i'm using a vlookup to find data which is in an array
mutliple times. The problem is that the vlookup only
returns the first occurance of the data i'm looking for.
Does anybody know of an alternative formula that will give
multiple returns?

thank you
chris

 

[Guest]

Hi Frank,
Thanks for your response. VLookups sounds exactly what
i'm looking for however i can't get vlookups or
vlookupleft recognized by excel.
=VLOOKUP(A7,'May 28'!D7:E23,3)this works fine.
=VLookups(A7,'May 28'!D7:E23,2)gives me a #name? error.
=VLookupleft(A7,'May 28'!D7:E23,2)gives me the same error.
is there something i'm missing in inputting these formulas?

your help is much appreciated

chris

 

[Frank Kabel]

Hi
have you inserted the macros in your workbook?
See: http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Guest]

this formula looks great but i'm having a little problem
interpreting it
=INDEX(B2:B100,SMALL(IF(A2:A100=C1,ROW(A2:A100)-1,""),ROW
(A2:A100)-1))

esentially the array i'd be looking at is from column c to
column e of another sheet. i was trying to get column e
of the array sheet(for the same row of the found data) in
column b of the current sheet, and column d of the array
sheet(again for the same row of the found data) in column
c of the current sheet.

i'm not exactly sure where in that formula i'd be defining
which row to return a value.

thanks for your help on this
chris

 

[Ragdyer]

I don't really understand what you're describing.
If you could you be more specific, say with cell addresses, I would be glad
to make some suggestions.

 

[Harlan Grove]

i'm using a vlookup to find data which is in an array
mutliple times. The problem is that the vlookup only
returns the first occurance of the data i'm looking for.
Does anybody know of an alternative formula that will give
multiple returns?

If you could live with finding them piece by piece rather than as a single
array, you could use the following array formulas.

H2 (topmost entry, not an array formula):
=VLOOKUP($D$1,$B$2:$C$50,2,0)

H3 (second entry, ARRAY FORMULA):
=INDEX($C$2:$C$50,MATCH(1,($B$2:$B$50=$D$1)-COUNTIF(H$2:H2,$C$2:$C$50),0))

Select H3 and fill down as far as needed. It'll evaluate to #N/A error values
when the matching entries in the table have been exhausted.

 

[kirk]

I've tried Harlan's approach but I keep getting #N/A as a result!
It seems that excel cant evaluate MATCH(1,0,0).

Cheers,

Kirk