How do you return blank if information is not found on index(match) function?

Constance.Brock

I am trying to create a cross reference formula that will link information from one sheet to another.

The first sheet (Roster) that has a bunch of position data, including the position number.

The second sheet (Validation Form) will be collecting additional position data over the course of the next year and it also includes the position number.

As users submit information for a position number with the Smartsheet Form, I need a formula that will capture that information from the Validation Form sheet in the record for that position number on the Roster sheet.

This is the formula I am currently on:

=INDEX({Validation Form Range 1}, MATCH([Position Number]@row, {Validation Form Range 2}))

It worked on my test row, but when I made it a column formula, it applies the result from the bottom record on the Validation Form sheet rather than looking for the correct value. Perhaps it is because the other position numbers are not yet populated on that form page?? Is there a way to have it return blank ("") if the position number is not found on the form page?

KPH

Hi @Constance.Brock

If you add a "0" at the end of your match the match will return the first exact match only and show #NO MATCH if there isn't an exact match (rather than the default which is the largest value smaller than or equal to the one you are searching for)

=INDEX({Validation Form Range 1}, MATCH([Position Number]@row, {Validation Form Range 2},0))

If you would prefer blank to #NO MATCH you could add an IFERROR statement to the formula.

=IFERROR(INDEX({Validation Form Range 1}, MATCH([Position Number]@row, {Validation Form Range 2},0)),"")

KPH

Hi @HZAR

If you want to return blank instead of #NO MATCH you can place the IFERROR function around the formula that is returning #NO MATCH.

=IFERROR(your formula goes here,"")

This means, if the formula returns an error, the value "" (ie nothing) will appear instead of the error message.

KPH

Hi @Constance.Brock

If you add a "0" at the end of your match the match will return the first exact match only and show #NO MATCH if there isn't an exact match (rather than the default which is the largest value smaller than or equal to the one you are searching for)

=INDEX({Validation Form Range 1}, MATCH([Position Number]@row, {Validation Form Range 2},0))

If you would prefer blank to #NO MATCH you could add an IFERROR statement to the formula.

=IFERROR(INDEX({Validation Form Range 1}, MATCH([Position Number]@row, {Validation Form Range 2},0)),"")

Constance.Brock

That worked, thank you so much!

KPH

That's wonderful, thanks for letting me know!

HZAR

Hi

May I know if I want to same results as below but I need to cross reference it to other 5 sheet, how can i do it?

From solution above:

If you would prefer blank to #NO MATCH you could add an IFERROR statement to the formula.=IFERROR(INDEX({Validation Form Range 1}, MATCH([Position Number]@row, {Validation Form Range 2},0)),"")

KPH

Hi @HZAR

If you want to return blank instead of #NO MATCH you can place the IFERROR function around the formula that is returning #NO MATCH.

=IFERROR(your formula goes here,"")

This means, if the formula returns an error, the value "" (ie nothing) will appear instead of the error message.

HZAR

Hi

'It works and thanks for the help :)