Question on IF(SEARCH formula

[9320blue]

Hello, I have a formula that finds a keyword in one column and places a different keyword in a seperate column and if it does not find it it is left blank. That works great but I want to do this with multiple words on the same columns. I have tried using AND/OR but it does not work.

Here is what works:

=IF(SEARCH("COLLINSVILLE",[@City],1),"MADISON")

This searches the City column for the work COLLINSVILLE and enters the work MADISON into my new column right next to it. I then added one for MT VERNON and JEFFERSON but it doesn't work.

=IF(SEARCH("COLLINSVILLE",[@City],1),"MADISON") OR (IF(SEARCH("MT VERNON",[@City],1),"JEFFERSON")

 

[gargamalebarbosa]

Please try this one.

IF(C1="COLLINSVILLE","MADISON",IF(C1="MT VERNON","JEFFERSON",""))

 

[Peter_SSs]

Welcome to the MrExcel board!

It looks like you would have quite a lot of different City/County combinations. I would suggest setting up another table with those combinations. Something like I have in columns K:L below. I have named that table as 'Counties". Then you can use the formula shown in the left hand table to retrieve the County names.

9320blue.xlsm
	C	D	E	F	G	H	I	J	K	L
1	City	State	PC	County					City	County
2	COLLINSVILLE			County 1					COLLINSVILLE	County 1
3	MT VERNON			County 2					GRANITE CITY	County 4
4	MARYVILLE			County 3					HIGHLAND	County 1
5	GRANITE CITY			County 4					MARYVILLE	County 3
6	STOW			Not found					MT VERNON	County 2
7	HIGHLAND			County 1
8
Sheet1

Cell Formulas
Range	Formula
F2:F7	F2	=IFNA(VLOOKUP([@City],Counties,2,0),"Not found")

BTW, I suggest that you investigate the following for any future questions.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.