I need help comparing two sheets and returning a new value- Can't find formula to help

I have two sheets. One contains a full list of employees in an organization. A separate sheet contains a list of people who have completed a survey. I need to compare the two sheets and wanted a formula that could look for the full name and then populate the word Yes into a cell if they have completed the survey.

Best Answer

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey @Susan Pratt

    Building some examples around André's solution, you might try these:

    Not knowing exactly how your data is arranged on the two sheets, I wondered if you needed an Index/Match or only a Match? If the data is the same in both sheets, a MATCH would do. The MATCH function returns a number designating the position in a list.

    =IF(AND(Name@row <> "", IFERROR(MATCH(Name@row, {Source Sheet Name column}, 0), 0) > 0), "Yes")

    If there is not a match, the NOMATCH# error is mitigated by the IFERROR function. This formula makes the error return a zero, which produces a false IF statement.

    If you do need to use an INDEX/MATCH to get to the correct name format, maybe this will work.

    =IF(IFERROR(INDEX({Source sheet data you're looking for}, MATCH(Name@row, {Source sheet name column}, 0)), 0) > 0, "Yes") or, you can embed the Index/Match as the criteria within a COUNTIFS and use that with an IF statement.

    Will any of these work for you?

    Kelly

Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @Susan Pratt

    I hope you're well and safe!

    Are the names unique?

    You could use cross-sheet formulas combined with either a VLOOKUP or INDEX/MATCH structure to connect the sheets, and when you update the source sheet, it will reflect on the destination sheet.

    Would that work/help?

    I hope that helps!

    Be safe and have a fantastic week!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Hi!

    That was what I was thinking but when I tried a match nested inside a VLookup formula, I am getting the Unparseable message. And now I cannot locate the example formula I was trying to adapt.

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Answer ✓

    Hey @Susan Pratt

    Building some examples around André's solution, you might try these:

    Not knowing exactly how your data is arranged on the two sheets, I wondered if you needed an Index/Match or only a Match? If the data is the same in both sheets, a MATCH would do. The MATCH function returns a number designating the position in a list.

    =IF(AND(Name@row <> "", IFERROR(MATCH(Name@row, {Source Sheet Name column}, 0), 0) > 0), "Yes")

    If there is not a match, the NOMATCH# error is mitigated by the IFERROR function. This formula makes the error return a zero, which produces a false IF statement.

    If you do need to use an INDEX/MATCH to get to the correct name format, maybe this will work.

    =IF(IFERROR(INDEX({Source sheet data you're looking for}, MATCH(Name@row, {Source sheet name column}, 0)), 0) > 0, "Yes") or, you can embed the Index/Match as the criteria within a COUNTIFS and use that with an IF statement.

    Will any of these work for you?

    Kelly

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!