Forum Discussion
Find word from list (string) - without macros
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 found | Purchase | Classification |
| not found | iPhone 11 64 GB - black | |
| not found | Lord of the Rings special edition books | |
| found | Garmin GPS watch | |
| not found | e-scooter Xiaomi | |
| not found | Silver photo frame | |
| found | TV with HDMI cable | |
| found | Baby pram set | |
| not found | Keyboard & mouse for PC | |
| not found | iPhone 11 256 GB white |
| items | Classification |
| e-scooter | expensive |
| phone | expensive |
| gps watch | expensive |
| frame | cheap |
| book | cheap |
| hdmi cable | cheap |
| pram | average |
| xbox game | average |
| keyboard | average |
Can anybody help me here?
Thanks in advance!!!
4 Replies
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.
Hi HansVogelaar
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!1000 is an arbitrary large number. You can change it to 1000000 or 1000000000 if 1000 is not large enough.
Thank you, HansVogelaar
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!!!
