Looking up multiple results with VLOOKUP

[Bob]

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.

 

[Peo Sjoblom]

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/Return more than one value from lookup.xls

 

[Teethless mama]

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

ctrl+shift+enter, not just enter

 

[Bob]

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.

 

[Bob]

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]

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

 

[T. Valko]

=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.

 

[Bob]

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