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

Options

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?

Tags:

Best Answers

  • KPH
    KPH ✭✭✭✭✭✭
    edited 11/02/23 Answer ✓
    Options

    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
    KPH ✭✭✭✭✭✭
    Answer ✓
    Options

    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.

Answers

  • KPH
    KPH ✭✭✭✭✭✭
    edited 11/02/23 Answer ✓
    Options

    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
    Options

    That worked, thank you so much!

  • KPH
    KPH ✭✭✭✭✭✭
    Options

    That's wonderful, thanks for letting me know!

  • HZAR
    HZAR ✭✭✭
    Options

    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
    KPH ✭✭✭✭✭✭
    Answer ✓
    Options

    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
    HZAR ✭✭✭
    Options

    Hi

    'It works and thanks for the help :)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!