# need help with conditional formatting

#### jfd145

##### New Member
So i have a spreadsheet that has about 22,000 different zip codes. starting at B2 all the way down to B21,989.

What I want to do is type a number in (a zip code), and if it is in the list, then I want a box to come up and say "YES" and if it isn't in the list to say "NO"

I believe i have to use a conditional format. But when i try to do it, nothing shows up in the box.

I know it is possible, I just dont know exactly how to do it. Can anyone give some insight?

thanks

### Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Welcome to the board.

If you are entering your Zip code (to be matched against the list) in A1, then A2=IF(ISNUMBER(MATCH(A1,B2:B21989,0)),"YES","NO")

Welcome to the Board!

You can do it with a formula:
Book4
ABCD
1Zip CodesIn ListYES/NO
27610992081YES
392592
492562
592081
692082
Sheet1

Formula is: =IF(ISNA(MATCH(B2,A:A,0)),"NO","YES")

You can then use conditional formatting for the YES/NO result.

Hope that helps,

Smitty

WOW, you guys are fast, thank you

one last question, is there a way to lock all but1 cell? For instance, I want to lock only A2 as in the first example to prevent the formula from being tampered with. But i want to still be able to put info in to cell A1

thanks again

By default all cells are locked, so just select the cell that you want to be editable and goto Format-->Cells-->Protection-->Uncheck "Locked", then goto Tools-->Protection-->Protect Sheet, add a password if you want.

Smitty

Replies
1
Views
321
Replies
0
Views
252
Replies
7
Views
146
Replies
2
Views
155
Replies
9
Views
461

1,202,909
Messages
6,052,499
Members
444,587
Latest member
ezza59

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back