Find word from list (string) - without macros

New Contributor

 

Hi everbody,

 

I am trying to find specific words from a list of items. I want to know if the word is in the strings given, and if it is, bring to cell C the classification of each item. I want to do all without macros. 

I need it to work like the VLOOKUP funtion when is fixed, that searches among the same list disregarding the position (cells) in which the item is located.

 

I have tried combining IF, ISNUMBER & SEARCH funtions but it does not work the way I want. I could not upload the file, here the tables:

 

found/not foundPurchaseClassification
not foundiPhone 11 64 GB - black 
not foundLord of the Rings special edition books 
foundGarmin GPS watch 
not founde-scooter Xiaomi 
not foundSilver photo frame 
foundTV with HDMI cable 
foundBaby pram set 
not foundKeyboard & mouse for PC 
not foundiPhone 11 256 GB white 

 

itemsClassification
e-scooterexpensive
phoneexpensive
gps watchexpensive
framecheap
bookcheap
hdmi cablecheap
pramaverage
xbox gameaverage
keyboard average

 

Can anybody help me here? 

 

Thanks in advance!!!

 

4 Replies

@asking4team 

Let's say the second list is in A2:B10 on Sheet 2 (with headers in A1:B1).

In C2 on the first sheet:

=LOOKUP(1000,SEARCH('Sheet 2'!$A$2:$A$10,B2),'Sheet 2'!$B$2:$B$10)

Fill down.

See the attached demo.

Thank you, @Hans Vogelaar 

 

Exactly what I needed!

I tried also with VLOOKUP + wildcards and was just about to start checking the Fuzzy LookUp but this does the job perfectly!

 

Thanks!!!

Hi @Hans Vogelaar

May I ask what does the "1000" do in that case?
Sometimes is working, sometimes is not...

Plus, I need something with a higher limit, does something else come to your mind? 

Thanks!

@asking4team 

1000 is an arbitrary large number. You can change it to 1000000 or 1000000000 if 1000 is not large enough.