Why won't my status symbol turn "Red"?

Lori Leighton

In my sheet, I have a column (RSVO Name RCVD) with a formula: =VLOOKUP([Last Name]8, {LCMS BOD - August 2021 Range 1}, 1, false)

This formula will return a person's last name once they register on another sheet. Before anyone registers "#NO MATCH" will be in the cell.

I have a status column with the RYG balls wit h a formula: =IF([RSVP Name RCVD]18 = [Last Name]18, "Green", "Red")

I do get a green ball when someone registers but initially it will be #NO MATCH in the cell.

I have tried rewriting the formula:

=IF([RSVP Name RCVD]@row = [Last Name]@row, "Green", IF([RSVP Name RCVD]@row <> [Last Name]@row, "Red"))

I still get the same results a green ball will show up when someone registers in the initial sheet but it will show #NO MATCH instead of a red ball? Can anyone help?

Genevieve P.

Hi @Lori Leighton

Formula errors act a bit like dominoes - if you have a cell with an error in it, then you try to reference that cell in another formula, it will display the error from the first formula instead of its own output.

Try wrapping your initial VLOOKUP in an IFERROR statement to return text instead of an error, like so:

=IFERROR(VLOOKUP([Last Name]8, {LCMS BOD - August 2021 Range 1}, 1, false), "NO MATCH")

You could also use this to return a blank cell, if you prefer:

=IFERROR(VLOOKUP([Last Name]8, {LCMS BOD - August 2021 Range 1}, 1, false), " ")

This should then resolve the issue with your Status formula!

Cheers,

Genevieve