VLOOKUP Formula Help Needed

Baris Meretuk

I have a VLOOKUP challenge  

1st sheet is a project portfolio and capturing project names and a project status in a traffic light system. On another sheet I want to calculate all the "green", "yellow" and "red" amounts in out portfolio. No issues there, but if I want to create a formula to get automatically all the names of the "green", "yellow" or "red" Project names into one cell in front of the calculated amount I am stuck. 

Does anyone know how I can get more then one TEXT from another sheet copied into my current one if the traffic light is on GREEN or any other color?

This formula is giving me a NO MATCH if there are more then one GREEN (or any other color) project: 



=VLOOKUP("Green", {Custom Feed_HR Program Dashboard_Cloud Tr Range 11}, 1)  

Thanks for your help.

Baris

Paul Newcome

Try something along these lines...

 

=JOIN(COLLECT({Master Sheet Range 1}, {Master Sheet Range 2}, "Green"), " - ")

 

What this will do is join all of the text in the cells in Range 1 from the Master sheet (the Project Name column) where the status in that same row of Range 2 (the Status column) is Green and will separate each cell's data with a - . Basically it would look something like this.

 

Data from first Green row - Data from second Green row - Data from third Green row............

 

And of course you can change the color in the formula to whichever color you need.

Baris Meretuk

Hi Paul,

Thanks a lot. Your solution worked perfectly and was way easier then the way I thought in the beginning. Much appreciated. 

Best,

Baris

Paul Newcome

Happy to help!