INDEX/MATCH/ISBLANK
Hello,
I'm having trouble with a formula.
I'm looking to return a cell value from an INDEX/MATCH function but if it's blank, to return another cell value from an INDEX/MATCH function.
Company adds travel justification based on employee ID's. If the employee ID hasn't been generated yet (pre hire travel), then the manager's employee ID is used.
This is the formula I am using, however, it's not showing the employee ID when available.
=IF(ISBLANK(INDEX({Source Sheet - Employee ID}, MATCH([Request ID]@row, {Source Sheet - Request ID}))), INDEX({Source Sheet - Manager ID}, MATCH([Request ID]@row, {Source Sheet - Request ID})))
Thank you in advance for your help!
Best Answer
-
Sorry about that. I was in a bit of a hurry when I posted that. There is also the step of outputting the original INDEX/MATCH if the original is not blank.
=IF(ISBLANK(1st_index_match), 2nd_index_match, 1st_index_match)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
Try adding in a zero for the third portion of each MATCH function. Leaving it blank will default to an approximate match which tends to miss things.
MATCH([Column Name]@row, {Range}, 0)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Hi Paul,
That didn't work.
This is what I used.
=IF(ISBLANK(INDEX({Source Sheet - Employee ID}, MATCH([Request ID]@row, {Source Sheet - Request ID}, 0))), INDEX({Source Sheet - Manager ID}, MATCH([Request ID]@row, {Source Sheet - Request ID}, 0)))
-
Sorry about that. I was in a bit of a hurry when I posted that. There is also the step of outputting the original INDEX/MATCH if the original is not blank.
=IF(ISBLANK(1st_index_match), 2nd_index_match, 1st_index_match)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
That worked! Thank you so much!
-
Happy to help. 👍️
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
This is what I am looking for, however, you have me lost with the "1st", "2nd" index match. However, when I try this formula, I get "incorrect argument".
=IF(ISBLANK(INDEX({Roster Team Member Lennox}, MATCH(Owner@row, {Roster Role}, INDEX({Roster Range 5}, MATCH(Owner@row, {Roster Role}, INDEX({Roster Team Member Lennox}, MATCH(Owner@row, {Roster Role}))))))))
Any help would be greatly appreciated!
Chris
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!