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
- Customer Resources
- 66.1K Get Help
- 430 Global Discussions
- 149 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 154 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!