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

Susan Pratt

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.

Kelly Moore

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

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!

Susan Pratt

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

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