I am getting an Invalid operation for the below if statement

Good day, I submitted this question on 2/23/21 in formulas and function and have not received a response which is unusual so I thought I would ask the same question in a different category.

I have a weekly vaccine by facility sheet that I need to load information from the master sheet if the facil # = 1 and the Row wk = Prev week and if true use the value in Tier/age group

=IF(AND({Facil #} = 1, {Row Wk} = {Prev week}), {Tier/age group})

I am using Cross reference to check another sheet for the above

Facil # is blue arrow - this is a number assigned to each hospital facility via a vlookup

Row Wk is Green arrow

Prev week is black arrow

If both are true I want to set the cell value to Tier/age group red arrow

Thank you in advance for the help

Find more posts tagged with

Accepted answers

Heather Duff

Hi! I'll take a stab at this. You may want to try using INDEX/COLLECT.

=INDEX(COLLECT({Tier/age group}, {Facil #}, 1, {Row Wk}, {Prev Wk}), 1)

I mocked it up with two sheets of my own and it seemed to be working. Please note that my {prev wk} range selects only the one cell that has the previous week listed.

Hope this helps!

Best,

Heather

All comments

Heather Duff

Hi! I'll take a stab at this. You may want to try using INDEX/COLLECT.

=INDEX(COLLECT({Tier/age group}, {Facil #}, 1, {Row Wk}, {Prev Wk}), 1)

I mocked it up with two sheets of my own and it seemed to be working. Please note that my {prev wk} range selects only the one cell that has the previous week listed.

Hope this helps!

Best,

Heather

bday2329

Thank you Heather That worked I appreciate your help!

Heather Duff

Fantastic! Glad it worked.

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.