INDEX/Match, to not display "#NO MATCH"

Mtmoroni

=INDEX({ Range 1}, MATCH([Column0]19, { Range 2}, 0))

How can I get this to NOT display "#NO MATCH" if there is no match?

Adrian Backus

IFERROR Function | Smartsheet Learning Center

https://help.smartsheet.com/function/iferror

Returns the first value if it isn’t an error, otherwise returns the second value.

For your use case:

=iferror(INDEX({ Range 1}, MATCH([Column0]19, { Range 2}, 0)),"")

Adrian Backus

IFERROR Function | Smartsheet Learning Center

https://help.smartsheet.com/function/iferror

Returns the first value if it isn’t an error, otherwise returns the second value.

For your use case:

=iferror(INDEX({ Range 1}, MATCH([Column0]19, { Range 2}, 0)),"")

Mtmoroni

Thank you again!

anaMG

Hi! What if I already have an IF in the formula? I tried adding IFERROR but it didn't work:

=IF([Student]@row = "Yes", INDEX({GMAT Students Master Sheet Range 5}, MATCH([Client Name]@row, {GMAT Students Master Sheet Range 2}, 0)), "Not student")

If a Student appears in another sheet (GMAT Students Master Sheet) I bring a value from that sheet to the current one. If he is not in the other sheet, it displays "Not student". I want for the ones that are students but do not appear in the other sheet to show blank instead of "#NO MATCH". How can I do that?

Genevieve P.

Hi @anaMG

You'll want to add the IFERROR around just the INDEX(MATCH portion of your formula, like so:

=IF([Student]@row = "Yes", IFERROR(INDEX({GMAT Students Master Sheet Range 5}, MATCH([Client Name]@row, {GMAT Students Master Sheet Range 2}, 0)), ""), "Not student")

Cheers,

Genevieve

anaMG

Thank you Genevieve!!!