If then using wildcard

I think that's right Peter...I believe if seen this in a CountIf formula.

If there's really only three search values you can get away with a nested set of if's. My attempts at fuzzy matching with three or more search values were not very successful otherwise - but a custom function could be designed to handle more search criteria.

This is an example of the same formula posted above but applied "three times" (however, it does not give two results if the address has two of the searched values -- i.e. main street would return "main"):

Formula in Cell B3 is:
=IF(ISNUMBER(SEARCH("Main",B2)),"Main",IF(ISNUMBER(SEARCH("Street",B2)),"Street",IF(ISNUMBER(SEARCH("Drive",B2)),"Drive","no")))

HTH, Alex

If you have a named range list containing the list of search term, i.e. "Main", "Street" and "Road" then you could use this formula

=LOOKUP(2^15,SEARCH(list,B2),list)

ec1957 said:

Sorry, I think I'm making it more confusing.

I want to be able to have a cell in column B look into a cell in column A. I want it to attempt to find any one of three different variables. Depending on the variable I have it looking for, the cell in column B would then = X.

Click to expand...

Then maybe ...

a) This adaptation of barry's formula?

</td></tr></table></td></tr></table>


b) If you didn't want 'X' in the last row of my sample because 'Main' is not the entire word...

</td></tr></table></td></tr></table>

=LOOKUP(2^15,SEARCH(list,B2),list)

Click to expand...

Very instructive little formula!