RYG with vlookup


I need my sheet to lookup an employee's id number on a second sheet and return a RYG ball depending on the code in the 8th column. I tried this with no luck:

=IF(VLOOKUP([Employee #]1, {EIC Range 3},8,False)="Yes","Green"),if(vlookup([Employee #]1,{EIC Range 3},8,False)="Not Yet","Yellow")if(vlookup([Employee#]1,{EIC Range 3},8,False)="No","Red")

EIC Range 3 = the columns in the second sheet

Column 8 has one of three "codes": Yes, No, Not Due




  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    edited 08/28/18

    =IF(VLOOKUP([Employee #]1, {EIC Range 3},8,False)="Yes","Green",if(vlookup([Employee #]1,{EIC Range 3},8,False)="Not Due","Yellow","Red"))


    Just a few minor tweaks...


    1. Changed "Not Yet" to "Not Due" based on what you said the option in column 8 were.


    2. Added a comma between the second and third IF statements.


    3. Removed closed parenthesis in between IF statements.


    4. Added closed parenthesis to end of formula to close out all IF statements. 


    5. Shortened it to say IF "Yes", then "Green", IF "Not Due", then "Yellow", otherwise "Red"


    When Nesting IF's, use the following format:


    =IF(This = That, Then Input Something, If not, do this...


    The "If not, do this" part is where you will put your next IF statement. It will basically be one long string. You then add multiple ))))) to the end to close it all out. The easiest way I have found to figure out how many ) you need at the end is to first make sure that all of your internal statements are closed (VLOOKUP, etc), then count how many times you typed "IF". That's how many ) you will need at the end.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!