vlookup w/ more than one return value

A

amy

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
 
P

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

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
 
N

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?
 
R

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

T. Valko

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

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
 
Top