What's wrong with my formula?

%3CLINGO-SUB%20id%3D%22lingo-sub-2379481%22%20slang%3D%22en-US%22%3EWhat's%20wrong%20with%20my%20formula%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2379481%22%20slang%3D%22en-US%22%3E%3CP%3EDevice%3A%20MacBook%20Air%20v.%2010.15.5%3C%2FP%3E%3CP%3EExcel%20v.%2016.49%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20using%20an%20exported%20data%20table%20from%20a%20teaching%20website%20to%20find%20schools%20to%20apply%20to.%20My%20goal%20is%20to%20use%20conditional%20formatting%20to%20%22cross-off%22%20schools%20that%20are%20outside%20of%20my%20commute%20radius.%20On%20the%20first%20sheet%20is%20the%20table.%20The%20second%20sheet%20has%20all%20the%20zipcodes%20that%20I%20can%20commute%20to.%20Right%20now%2C%20I%20have%20this%20as%20my%20formula%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DXLOOKUP(ADDRESS(ROW(H7)%2CCOLUMN(H7)%2C3)%2C'Sheet1!'%24A%242%3A%24A%2482%2CSheet1!%24B%242%3A%24B%2482%2CFALSE)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EHowever%2C%20it%20says%20that%20this%20isn't%20a%20formula.%20Did%20I%20do%20something%20wrong%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20rather%20learn%20what%20it%20is%20that%20I've%20done%20wrong%20and%20how%20to%20improve%20the%20formula%20than%20have%20someone%20do%20it%20for%20me.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20the%20help!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2379481%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2379567%22%20slang%3D%22en-US%22%3ERe%3A%20What's%20wrong%20with%20my%20formula%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2379567%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1061233%22%20target%3D%22_blank%22%3E%400riginal%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20use%20of%20ADDRESS()%20as%20a%20search%20criteria%20makes%20no%20sense.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DCOUNTIFS(Sheet1!%24A%242%3A%24A%2482%2CLEFT(%5B%40%5BStreet%20Zip%5D%5D%2C5))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2379574%22%20slang%3D%22en-US%22%3ERe%3A%20What's%20wrong%20with%20my%20formula%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2379574%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOkay%2C%20so%20does%20the%20Conditional%20Formatting%20automatically%20change%20the%20formula%20given%20the%20position%20of%20the%20cell%3F%20In%20other%20words%2C%20when%20I%20am%20creating%20a%20conditional%20formatting%20formula%20that%20edits%20a%20row%20based%20on%20one%20cell%2C%20does%20the%20formula%20update%20at%20each%20row%20to%20know%20to%20look%20at%20that%20row%3F%20Hopefully%2C%20that%20makes%20some%20type%20of%20sense.%20EDIT%3A%20I%20also%20tried%20that%20formula%20that%20you%20gave%2C%20but%20it%20isn't%20working%20still.%20It%20says%20that%20the%20syntax%20of%20the%20name%20is%20incorrect.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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

@0riginal 

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

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

 

@Detlef Lewin 

 

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.

@0riginal 

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.