Lookup using Find in lookup value

[zfeld75]

As an example see the 2 sample sheets below.
They are silly examples but this is similar to what I need to accomplish.
I have a value that is text in an activities column and if there is any match in the text to a lookup value, in the lookup table, then it should return it's corresponding value
There will be no duplicate lookup values in the activities column (and if there is I am fine if it finds the first lookup value).
Thank you.

Here is the lookup sheet - see below for the other table

Lookup value	Return value
milk	95
meat	60
poultry	20
fish	50
candy	10
bread	30
fruit	45

Here is the sheet where the lookup value is embedded in the Activities column

Activities	This column needs a value based on lookup below
grow, coat, bread, laugh
fruit, run, wake,
think, sneeze, meat, wink
walk, talk, fruit
sleep, candy, read, walk
thing, neat, milk, dry

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Maybe

Fluff.xlsm
	A	B	C	D	E
1	Lookup value	Return value		Activities
2	milk	95		grow, coat, bread, laugh	30
3	meat	60		fruit, run, wake,	45
4	poultry	20		think, sneeze, meat, wink	60
5	fish	50		walk, talk, fruit	45
6	candy	10		sleep, candy, read, walk	10
7	bread	30		thing, neat, milk, dry	95
8	fruit	45
Sheet6

Cell Formulas
Range	Formula
E2:E7	E2	=LOOKUP(2,1/FIND($A$2:$A$8,D2),$B$2:$B$8)

 

[zfeld75]

Wow, that works....
My version is Microsoft® Excel® for Microsoft 365 MSO (Version 2401 Build 16.0.17231.20236) 64-bit
How does it work, I would love to understand it
Find - usually takes text not a range
Lookup, having number 2 as the first argument, what's up with that?
Also, 1 divided by the Find

 

[Fluff]

My version is Microsoft® Excel® for Microsoft 365 MSO (Version 2401 Build 16.0.17231.20236) 64-bit

Please don't forget to update your profile.

How does it work, I would love to understand it

Have a look at examples 2 & 3 here LOOKUP Function

 

[zfeld75]

Thanks I am so grateful.
I even improved on it using modern XLOOKUP.
=XLOOKUP(TRUE,ISNUMBER(FIND($A$2:$A$8,D2)),$B$2:$B$8)
Teach a man how to fish.....

 

[Fluff]

Glad to help & thanks for the feedback.