a search function

[jacko]

i tried on couple search/lookup functions (or combination of few) but i still
could not produce the result i want... hope someone expert out there can help


My sample data: A1:A3 value are fixed
A1=James
A2=Cole
A3=Don

B1:B3 value are variable (input area) where either is blank or with specific
keyword like Yes or No.

I would like to have a function to search through B1 to B3 for the keyword
and output the corresponding A1:A3 value into a cell.

Eg: if B1 and B3 contain Yes, then output A1 and A3 (James, Don) into a
cell. B2 is ignored since it is blank.

I tried like =match =vlookup =offset, I could only display the first result.
Like example above, only James is displayed.

I try not to use VBA (if possible) as I am not a good VBA programmer...

Thanks in advance!!

 

[bj]

if you only have a few rows try
=if(B1<>"",A1&", ")&if(B2<>"",A2&", ")&if(B3<>"",A3&", ")&if(B4<>"",A4)
This will probably leave you with a "," at the end of your line. the
formula can be made more difficult to remove it, or a second cell can be used
with something like
=if(right(C1,2)=", ", left(C1,len(C1)-2),C1