Forum Discussion
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
The use of ADDRESS() as a search criteria makes no sense.
=COUNTIFS(Sheet1!$A$2:$A$82,LEFT([@[Street Zip]],5))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.
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.
