On an Index Match, why is it returning a match when the match criteria row is blank?

Options

Hi, I am trying to get a a person's region included in a sheet by matching their name in the Staff Column to the source sheet. However, when the row in the Staff Column is blank, I am getting a return from the first line of the source sheet. Is there a way to get the return to be blank if there is nothing to match in the Staff Column? I used the same formula for the Staff Level Column, and it returns blanks where there is no staff listed.

Here is the formula I am using for the Staff Region: =INDEX({TSG Credentials & Associations Log Region}, MATCH(Staff@row, {TSG Credentials & Associations Log Name}))

Here is the formula I am using for the Staff Level: =INDEX({TSG Credentials & Associations Log Title}, MATCH(Staff@row, {TSG Credentials & Associations Log Name}))

Thanks in advance for any help!

Tags:

Best Answer

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓
    Options

    @SamE

    You must have a row in the source sheet where Staff is blank and Region is Central, otherwise your formula would return #NO MATCH on a blank row. So first step is check your source data.

    If your source data can't be changed, you can use an IF statement with your INDEX/MATCH. In English - If Staff@row is blank, just leave this Staff Region column blank, otherwise, run this INDEX/MATCH formula.

    =IF(ISBLANK(Staff@row), "", INDEX({TSG Credentials & Associations Log Region}, MATCH(Staff@row, {TSG Credentials & Associations Log Name})))

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!