Looking up multiple results with VLOOKUP
 

Is it possible to have VLOOKUP return multiple results? (I suspect the
answer is "No"). My "lookup table" consists of project numbers in column A
and supported systems in column B (the project number is repeated in each row
for which there is a different supported system).
If VLOOKUP can't do the job, can someone possibly show me how to write a
macro that will? I would appreciate any help.
Thanks.

Looking up multiple results with VLOOKUP
 

Here;'s a way

http://nwexcelsolutions.com/advanced_function_page.htm

look at number 6

You can download a sample here

http://nwexcelsolutions.com/Download...20lookup .xls


--
Regards,

Peo Sjoblom



"Bob" wrote in message
...
Is it possible to have VLOOKUP return multiple results? (I suspect the
answer is "No"). My "lookup table" consists of project numbers in column
A
and supported systems in column B (the project number is repeated in each
row
for which there is a different supported system).
If VLOOKUP can't do the job, can someone possibly show me how to write a
macro that will? I would appreciate any help.
Thanks.

Looking up multiple results with VLOOKUP
 

=INDEX(C1:C100,MATCH(1,(A1:A100="criteria 1")*(B1:B100="criteria 2"))

ctrl+shift+enter, not just enter


"Bob" wrote:

Is it possible to have VLOOKUP return multiple results? (I suspect the
answer is "No"). My "lookup table" consists of project numbers in column A
and supported systems in column B (the project number is repeated in each row
for which there is a different supported system).
If VLOOKUP can't do the job, can someone possibly show me how to write a
macro that will? I would appreciate any help.
Thanks.

Looking up multiple results with VLOOKUP
 

Thanks for the help! My current VLOOKUP formula is as follows:
=VLOOKUP(F2,$A$2:$B$11343,2)
I'm not sure I understand how to translate your formula below to use the
lookup_value, table_array, and col_index_number contained in my formula.
Could you kindly clarify? Thanks.


"Teethless mama" wrote:

=INDEX(C1:C100,MATCH(1,(A1:A100="criteria 1")*(B1:B100="criteria 2"))

ctrl+shift+enter, not just enter


"Bob" wrote:

Is it possible to have VLOOKUP return multiple results? (I suspect the
answer is "No"). My "lookup table" consists of project numbers in column A
and supported systems in column B (the project number is repeated in each row
for which there is a different supported system).
If VLOOKUP can't do the job, can someone possibly show me how to write a
macro that will? I would appreciate any help.
Thanks.

Looking up multiple results with VLOOKUP
 

Peo,
Thanks for your help! Instead of returning the results in separate cells,
can you tell me if there is a way to modify your formula so that the results
are concatentated (using a comma and space as a separator) and displayed in
one cell?
Thanks again,
Bob


"Peo Sjoblom" wrote:

Here;'s a way

http://nwexcelsolutions.com/advanced_function_page.htm

look at number 6

You can download a sample here

http://nwexcelsolutions.com/Download...20lookup .xls


--
Regards,

Peo Sjoblom



"Bob" wrote in message
...
Is it possible to have VLOOKUP return multiple results? (I suspect the
answer is "No"). My "lookup table" consists of project numbers in column
A
and supported systems in column B (the project number is repeated in each
row
for which there is a different supported system).
If VLOOKUP can't do the job, can someone possibly show me how to write a
macro that will? I would appreciate any help.
Thanks.

Looking up multiple results with VLOOKUP
 

You need to use 2 formulas then, first get all lookups then concatenate them

=A1&", "&A2&", "&A3 etc

you won't get a formula that will do this in one fell swoop


--
Regards,

Peo Sjoblom



"Bob" wrote in message
...
Peo,
Thanks for your help! Instead of returning the results in separate cells,
can you tell me if there is a way to modify your formula so that the
results
are concatentated (using a comma and space as a separator) and displayed
in
one cell?
Thanks again,
Bob


"Peo Sjoblom" wrote:

Here;'s a way

http://nwexcelsolutions.com/advanced_function_page.htm

look at number 6

You can download a sample here

http://nwexcelsolutions.com/Download...20lookup .xls


--
Regards,

Peo Sjoblom



"Bob" wrote in message
...
Is it possible to have VLOOKUP return multiple results? (I suspect the
answer is "No"). My "lookup table" consists of project numbers in
column
A
and supported systems in column B (the project number is repeated in
each
row
for which there is a different supported system).
If VLOOKUP can't do the job, can someone possibly show me how to write
a
macro that will? I would appreciate any help.
Thanks.

Looking up multiple results with VLOOKUP
 

=VLOOKUP(F2,$A$2:$B$11343,2)

That tells me your table is sorted so that means all the project numbers are
grouped.

Try this:

=IF(ROWS($1:1)<=COUNTIF(A$2:A$11343,F$2),INDEX(B$2 :B$11343,MATCH(F$2,A$2:A$11343,0)+ROWS($1:1)-1),"")

Copy down until you get blanks. You'd have to copy to the same number of
cells that equals the max count of any project. For example, project XX
appears the most, 10 times. So you'd have to copy the formula to at least 10
cells.

--
Biff
Microsoft Excel MVP


"Bob" wrote in message
...
Thanks for the help! My current VLOOKUP formula is as follows:
=VLOOKUP(F2,$A$2:$B$11343,2)
I'm not sure I understand how to translate your formula below to use the
lookup_value, table_array, and col_index_number contained in my formula.
Could you kindly clarify? Thanks.


"Teethless mama" wrote:

=INDEX(C1:C100,MATCH(1,(A1:A100="criteria 1")*(B1:B100="criteria 2"))

ctrl+shift+enter, not just enter


"Bob" wrote:

Is it possible to have VLOOKUP return multiple results? (I suspect the
answer is "No"). My "lookup table" consists of project numbers in
column A
and supported systems in column B (the project number is repeated in
each row
for which there is a different supported system).
If VLOOKUP can't do the job, can someone possibly show me how to write
a
macro that will? I would appreciate any help.
Thanks.

Looking up multiple results with VLOOKUP
 

Peo,
Thanks. However, since the number of supported systems varies by project
(and can change over time), your proposed solution doesn't really help.
Thanks all the same.
Bob


"Peo Sjoblom" wrote:

You need to use 2 formulas then, first get all lookups then concatenate them

=A1&", "&A2&", "&A3 etc

you won't get a formula that will do this in one fell swoop


--
Regards,

Peo Sjoblom



"Bob" wrote in message
...
Peo,
Thanks for your help! Instead of returning the results in separate cells,
can you tell me if there is a way to modify your formula so that the
results
are concatentated (using a comma and space as a separator) and displayed
in
one cell?
Thanks again,
Bob


"Peo Sjoblom" wrote:

Here;'s a way

http://nwexcelsolutions.com/advanced_function_page.htm

look at number 6

You can download a sample here

http://nwexcelsolutions.com/Download...20lookup .xls


--
Regards,

Peo Sjoblom



"Bob" wrote in message
...
Is it possible to have VLOOKUP return multiple results? (I suspect the
answer is "No"). My "lookup table" consists of project numbers in
column
A
and supported systems in column B (the project number is repeated in
each
row
for which there is a different supported system).
If VLOOKUP can't do the job, can someone possibly show me how to write
a
macro that will? I would appreciate any help.
Thanks.