IF(Index-Match) - Multiple Entries in INDEX returns Non-Blank
I’m using an Index-Match formula and ran into an issue where the Source sheet (WIP Details) has multiple entries in the INDEX column (some entries w/ JobCtMo populated, some with JobCtMo blank). I want to capture only the entries where JobCtMo is NOT BLANK. I used the formula below, but it is returning the #INVALID DATA TYPE error. I also verified that all columns in the formula are text/number data types.
=IF({WIP Details_JobCtMo}, INDEX({WIP Details_JobCtMo}, MATCH([Well/ Pad Name/ Job Type]@row, {WIP Details_wellPadjob}, 0)), "")
Best Answer
-
Try this
=INDEX(COLLECT({WIP Details_JobCtMo}, {WIP Details_JobCtMo},<>"", {WIP Details_wellPadjob},[Well/ Pad Name/ Job Type]@row),1)
Answers
-
Try this
=INDEX(COLLECT({WIP Details_JobCtMo}, {WIP Details_JobCtMo},<>"", {WIP Details_wellPadjob},[Well/ Pad Name/ Job Type]@row),1)
-
That worked! Thanks so much! I added IFERROR to it and got exactly the results I needed!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.5K Get Help
- 367 Global Discussions
- 202 Industry Talk
- 432 Announcements
- 4.4K Ideas & Feature Requests
- 137 Brandfolder
- 129 Just for fun
- 128 Community Job Board
- 447 Show & Tell
- 29 Member Spotlight
- 1 SmartStories
- 285 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!