Utilizing reference sheet to pull data with two columns of criteria

Ali Simpson

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

Paul Newcome

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")

Ali Simpson

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 :)

Paul Newcome

Glad you were able to get it sorted.