Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Formula Cannot Reference a Range on other sheets

Copper Contributor

Hi lovely people,

I am trying to make the following forumula work:

=LOOKUP(2,1/(((COUNTIF($A$1:A1, Input!$A$2:$A$200)=0)*(COUNTIF(Input!$A$2:$A$200, Input!$A$2:$A$200)>0.5))), Input!$A$2:$A$200)

The parts in bold are references to a range in another sheet on the document called "Input". Whenever I type the "Input!" in front of the range, it immediately deactivates. This is incredibly frustrating.

I've also tried =LOOKUP(2,1/(((COUNTIF($A$1:A1, 'Input!'$A$2:$A$200)=0)*(COUNTIF('Input!'$A$2:$A$200, 'Input!'$A$2:$A$200)>0.5))), 'Input!'$A$2:$A$200

Any ideas on how to change it?


1 Reply


I copy/paste the formula and have no problems with it