vlookup w/ more than one return value

[Guest]

Is there a way to do a vlookup and have it return all different values
instead if just the top most value?

Thank you,
amy

 

[Pete_UK]

Not with VLOOKUP, but you may be able to achieve this with SUMPRODUCT
- when you say return all different values, do you mean add them all
up, return then on different rows, return a composite string made up
of all the matching values separated by a space?

More details please.

Pete

 

[T. Valko]

Here's a small sample file that demonstrates 2 methods.

If the lookup values are random or, if the lookup values are grouped
together or sorted.

Sample file - lookup with multiple lookup values 19kb


http://cjoint.com/?ivesCzGGpk

 

[Guest]

This was perfect! Thank you for your help!

Here's a small sample file that demonstrates 2 methods.

If the lookup values are random or, if the lookup values are grouped
together or sorted.

Sample file - lookup with multiple lookup values 19kb


http://cjoint.com/?ivesCzGGpk

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[nikko]

hello!

i can't view the sample files, could you please re-attached.. thanks

i'm currently using below formula to return the multiple values..

=INDEX($A$2:$B$99,SMALL(IF($A$2:$A$99=$A$103,ROW($A$2:$A$99)),ROW(1:1)),2)

When i drag the formulas downwards, the values will show.
However if i drag the formulas across the row, it does not seem to wrk, any
idea?

 

[Rajula]

I am also looking for solution for looking up multiple values. I am unable to
access this link & its not in English.. Can you post the sample file.

Thanks & regards
Rajula

 

[T. Valko]

Explain what you want to do and be sure to include where the data is located
and where you want the results.

 

[Rajula]

I need to vlookup more than one value and return the values in different rows.

I have all the data in different files(6-7 files)
I want to collate all the date into a single spreadsheet.

For Example i have data in Fila A for Example. I want to add a lookup in
File B to get the data as below. I tried this formula, but its
giving me a #Ref error.
=INDEX([Book2]Sheet1!$A$1:$B$759,SMALL(IF([Book2]Sheet1!$A$1:$A$759=H7,ROW([Book2]Sheet1!$A$1:$B$759)),ROW(1:1)))


A B

Proj 1 x
Proj 1 z
Proj 2 q
Proj 1 q
Proj 3 x
Proj 4 y
Proj 4 z
Proj 1 y


File B


Proj 1 x
y
z
q
Proj 2 q
Proj 3 x
Proj 4 y
z