Search results of a column display a set value in another column if matched

Hello,

I need a little help. Is there a way to search a column for a specific word or phrase, then if that phrase matches assign a value next to it.

For example:

A1 | B1

John Realty | Realtor
Jim Appraiser |
Bob the Realtor | Realtor

If search results from column A = appraiser, then add "Appraiser" to column B1 where the search value is 'true'. The result should be:

A1 | B1

John Realty | Realtor
Jim Appraiser | "Appraiser"
Bob Realtor | Realtor

I have approximately 10,000 records and a search and replace doesn't seem very time effective.

Thanks for any help.

Ryan.

=if(isnumber(search("realt",a1)),"Realtor",if(isnumber(search("appraiser",a1)),"Appraiser"))

Add more nested IFs as needed.

Hi,

This almost works. Maybe I'm doing something wrong.

I've attached a sample sheet for review.

Thanks,
Ryan.

It seems not all items have "realt" in them. I need to know the full set of your parameters to code for every one of them.


Here's the function logic, so you can try to piece it together:

SEARCH(for what, where) = Provides starting character. So, SEARCH("some","awesome") = 4. No match creates an error.

ISNUMBER(what) = Test to see if something is a number, True/False. Errors are not numbers, they create false.

IF(if what,when it's true, when it's false)

Assuming A1 = Bob the Realtor

B1 = IF(ISNUMBER(SEARCH("realt",A1)),"REALTOR","NOT VERY SPECIAL")

=IF(ISNUMBER(9),"REALTOR","NOT VERY SPECIAL")

ISNUMBER(9) = TRUE, therefore the output will be "REALTOR"


You can nest IFs by simply adding a second IF at the False statement.

Assuming A1 = Five

B1 = if(a1="One",1,if(a1="Two",2,if(a1="Three",3,if(a1="Four",4,if(a1="Five",5,"No Match")))))

= 5

The output is assumed FALSE if no actual condition is specified. So, to make Realtor the output we just have to delcare it.

Original:
=if(isnumber(search("realt",a1)),"Realtor",if(isnumber(search("appraiser",a1)),"Appraiser"[no FALSE declaration]))

Revised:
=if(isnumber(search("realt",a1)),"Realtor",if(isnumber(search("appraiser",a1)),"Appraiser","Realtor"))

Ah, got it. Didn't think about it already being a false value.

Thanks a lot for the help - saved me an enormous amount of time.

Ryan.

Process Improvement is my middle name.