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

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

On Aug 21, 12:04 am, amy wrote:
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

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

--
Biff
Microsoft Excel MVP


"amy" wrote in message
...
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

This was perfect! Thank you for your help!

"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"amy" wrote in message
...
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

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"amy" wrote in message
...
This was perfect! Thank you for your help!

"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"amy" wrote in message
...
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

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?



--
nikko


"T. Valko" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"amy" wrote in message
...
This was perfect! Thank you for your help!

"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"amy" wrote in message
...
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

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" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"amy" wrote in message
...
This was perfect! Thank you for your help!

"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"amy" wrote in message
...
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

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

--
Biff
Microsoft Excel MVP


"Rajula" wrote in message
...
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" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"amy" wrote in message
...
This was perfect! Thank you for your help!

"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"amy" wrote in message
...
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

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

"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"Rajula" wrote in message
...
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" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"amy" wrote in message
...
This was perfect! Thank you for your help!

"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"amy" wrote in message
...
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

Ok, but you won't be able to get the results to appear the way you want with
a single formula. You'd have to use a separate formula for each change in
proj.

For example:

...........H..............I............
7.....Proj 1.....formula1 = x
8...................formula1 = z
9...................formula1 = q
10.................formula1 = y
11...Proj 2.....formual2 = q
12...Proj 3.....formula3 = x

What you could do is this using just a single formula:

..........H..........I..........J..........K...... ...L
7...Proj 1.......x.........z...........q.........y
8...Proj 2.......q.................................
9...Proj 3.......x.................................
10.Proj 4.......y.........z......................

Let me know which result format you want.

--
Biff
Microsoft Excel MVP


"Rajula" wrote in message
...
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

"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"Rajula" wrote in message
...
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" wrote:

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"amy" wrote in message
...
This was perfect! Thank you for your help!

"T. Valko" wrote:

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

--
Biff
Microsoft Excel MVP


"amy" wrote in message
...
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