Oct 03 2022 02:18 AM
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!!!
Oct 03 2022 03:14 AM
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.
Oct 03 2022 03:24 AM
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!!!
Oct 04 2022 11:33 PM - edited Oct 05 2022 12:12 AM
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!
Oct 05 2022 01:42 AM
1000 is an arbitrary large number. You can change it to 1000000 or 1000000000 if 1000 is not large enough.