IF with FIND and range Help

[titantrojan]

Hi ladies and gents. This is my first post and I'm hoping one of you very knowledgeable people can help me out because this is beyond my understanding.

I am trying to lookup up if a cell contains a text string that is within a separate range that is named "Criteria". If true, then I would like to place that text value in the cell that contains the forumla (in this case column G), if false, it can say false or be blank.

I found and tried the following forumla but it only found the text in cell G4 for some reason (even though it can be found in various cells): IF(ISNUMBER(FIND(Criteria,B4)),Criteria)

Here is a screenshot of my worksheet, the column in red is the one I can't get to work properly

Screen Shot 2013-01-31 at 5.55.49 PM.png

Any advice?

Thanks for your help!

[benishiryo]

hi titantrojan, welcome to the forum. uploading a sample Excel file in the thread will enable us to help you better. to upload, press "Go Advanced" beside the "Post Quick Reply" button & click on the paperclip icon
ideally, it should contain your desired results

but you can try this:
=LOOKUP(2,1/(FIND(Criteria,B4)),Criteria)
to handle errors, try:
=IF(ISNA(LOOKUP(2,1/(FIND(Criteria,B4)),Criteria)),"",LOOKUP(2,1/(FIND(Criteria,B4)),Criteria))
or not sure if Mac2011 has IFERROR:
=IFERROR(LOOKUP(2,1/(FIND(Criteria,B4)),Criteria),"")

ps: do note that FIND is case-sensitive. you can use SEARCH if you don't want it to be so. same fields to fill.

[titantrojan]

benishiryo, you are awesome!!! I don't understand how the LOOKUP part of the formula functions (I'll do some research), but it works.

Thank you so much!