need help with conditional formatting

[jfd145]

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

 

[Oaktree]

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")

 

[Smitty]

Welcome to the Board!

You can do it with a formula:

Book4
	A	B	C	D
1	Zip Codes	In List	YES/NO
2	76109	92081	YES
3	92592
4	92562
5	92081
6	92082
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

 

[jfd145]

WOW, you guys are fast, thank you

 

[jfd145]

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

 

[Smitty]

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