INDEX/MATCH only on rows that are not children

Beth Fantozzi 1

Hi there - I want to report an INDEX/MATCH result only on rows that are not children.

Here's my formula, any thoughts on how I can get it so I don't see "#NO MATCH" on the child rows?

=INDEX({ Nickname}, MATCH([SITE ID]@row, {Master | SITE ID}, 0))

Here's a screenshot of my sheet:

Kelly Moore

Hey @Beth Fantozzi 1

There's a couple of ways to do what you need.

The IFERROR function is a way to mask an error message. It allows you to write whatever you would like in a cell to replace the error message, including a blank, or run alternate formulas. Notice your entire formula is wrapped within the IFERROR function followed by what you want the IFERROR to do if an error is encountered.

=IFERROR(INDEX({ Nickname}, MATCH([SITE ID]@row, {Master | SITE ID}, 0)),"")

The other handy formula is to differentiate Parent rows from Child rows. This is critical if the Parent row is using one formula while a Child row uses an alternate formula.

=IF(COUNT(CHILDREN())>0, INDEX({ Nickname}, MATCH([SITE ID]@row, {Master | SITE ID}, 0)))

Since only Parent rows will have Children, you can differentiate.

Kelly

Kelly Moore

Hey @Beth Fantozzi 1

There's a couple of ways to do what you need.

The IFERROR function is a way to mask an error message. It allows you to write whatever you would like in a cell to replace the error message, including a blank, or run alternate formulas. Notice your entire formula is wrapped within the IFERROR function followed by what you want the IFERROR to do if an error is encountered.

=IFERROR(INDEX({ Nickname}, MATCH([SITE ID]@row, {Master | SITE ID}, 0)),"")

The other handy formula is to differentiate Parent rows from Child rows. This is critical if the Parent row is using one formula while a Child row uses an alternate formula.

=IF(COUNT(CHILDREN())>0, INDEX({ Nickname}, MATCH([SITE ID]@row, {Master | SITE ID}, 0)))

Since only Parent rows will have Children, you can differentiate.

Kelly

Beth Fantozzi 1

@Kelly Moore Thank you for taking the time to look at this formula and for your explanation of these two options. This is now working!