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

Find more posts tagged with

Accepted answers

All comments

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.