VLOOKUP Formula Help Needed

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

Find more posts tagged with

Project Management

Comments

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!

Sign in to join the conversation!

Quick Links

Trending discussions

Request for Flexible Editing Access for Free Members on Project Plans
Hello Members, I hope you’re doing well. We’re a small team of six members with a Business License — three licensed admins and three free users — using Smartsheet to manage and track multiple project plans. As admins, we create new project plans directly in Smartsheet and import some from Microsoft Project. However, our…
Updating of cells from an approval form submission
I have a sheet of users listed and a field to confirm if they are enabled or disabled. Then I have a second sheet populated from a form, where users submit a request to have access enabled. this uses an automated function to send an approval email to the manager who approves or rejects. What I would like to do is use this…
Spanish translation
Does anyone know if there is a way within Smartsheet to create translated versions of the sheet and the form? I really need English and Spanish but I would love for the data to feed into the same sheet which would be English. Is that even possible? Freda