On an Index Match, why is it returning a match when the match criteria row is blank?
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!
Best Answer
-
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
-
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!
-
@Jeff Reisman Thanks so much! That solved my problem.
-
Excellent, glad it worked for you!
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!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 456 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!