Utilizing reference sheet to pull data with two columns of criteria
I am looking to pull data from my reference sheet (Ref) into a master view utilizing multiple data checks.
Currently I'm working with below formula but getting a #INVALID VALUE error.
=INDEX(COLLECT({Ref Sheet: Staff}, {Ref Sheet: Mid}, MID@row, {Ref Sheet: Status}, "Onboard"), 1)
reference page
master view
the reason it needs to be a two part criteria is because I will be doing the same thing later with a different status check
Answers
-
That particular error is the equivalent of getting NO MATCH when using INDEX/MATCH. Lets temporarily try two separate COUNTIFS (on the same row) so that we can see which part isn't working (or if it is both).
=COUNTIFS({Ref Sheet: Mid}, @cell = MID@row)
=COUNTIFS({Ref Sheet: Status}, "Onboard")
-
Thanks Paul! Turns out my data pulled ref sheet had an ' at the beginning of one of the cells that was hidden from me. It's working now with that formula :)
-
Glad you were able to get it sorted.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 466 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!