New Contributor

What's wrong with my formula?

Device: MacBook Air v. 10.15.5

Excel v. 16.49

I'm using an exported data table from a teaching website to find schools to apply to. My goal is to use conditional formatting to "cross-off" schools that are outside of my commute radius. On the first sheet is the table. The second sheet has all the zipcodes that I can commute to. Right now, I have this as my formula:

``=XLOOKUP(ADDRESS(ROW(H7),COLUMN(H7),3),'Sheet1!'\$A\$2:\$A\$82,Sheet1!\$B\$2:\$B\$82,FALSE)``

However, it says that this isn't a formula. Did I do something wrong?

I rather learn what it is that I've done wrong and how to improve the formula than have someone do it for me.

Thanks for the help!

3 Replies

Re: What's wrong with my formula?

The use of ADDRESS() as a search criteria makes no sense.

``=COUNTIFS(Sheet1!\$A\$2:\$A\$82,LEFT([@[Street Zip]],5))``

Re: What's wrong with my formula?

Okay, so does the Conditional Formatting automatically change the formula given the position of the cell? In other words, when I am creating a conditional formatting formula that edits a row based on one cell, does the formula update at each row to know to look at that row? Hopefully, that makes some type of sense. EDIT: I also tried that formula that you gave, but it isn't working still. It says that the syntax of the name is incorrect.

Re: What's wrong with my formula?

I overlooked that you wanted a conditional formatting rule.
Small change.

`` =COUNTIFS(Sheet1!\$A\$2:\$A\$82,LEFT(\$H7,5))``

Select H7:H105 and the put the formula in the conditional formatting rule. The rule will apply in memory to all the cells.