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

SamE

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!

Jeff Reisman

@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})))

Jeff Reisman

@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})))

SamE

@Jeff Reisman Thanks so much! That solved my problem.

Jeff Reisman

Excellent, glad it worked for you!