I am getting an Invalid operation for the below if statement

bday2329

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

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

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.