Lookup with a range

[yashgt]

I have a few items for lookup defined in a range, e.g.
Col A is the name and B is the Category.

Biscuit	Snack
Sandwich	Snack
Milk	Beverage
Potato	Vegetable

In another sheet, I have column A with free form text which may have a mention of the words listed above. The text cannot have more than one of the above words.
E.g.

I like Biscuits
Need to buy Milk
Milk is healthy

I want to set the value of column B as the Category of the word from the first range, e.g. Essentially I want to identify the word from first range that matches the text in the second range and use the index of the matched word to get a value for the Category.

I like Biscuits	Snack
Need to buy Milk	Beverage
Milk is health	Beverage

How to do it?

 

[SanjayGMusafir]

Please update your current Excel version in your profile and here. the solution will depend on that.

 

[yashgt]

I would like to add I use Excel 2013

 

[SanjayGMusafir]

I would like to add I use Excel 2013

Excel 2013 - I have to rethink and address your query. I shall revert back soon. Actually with age my memory lets me down and I have recall the old formats and functions how they work. But I shall revert.

 

[SanjayGMusafir]

Hi Fluff, Alex Blakenburg, Peter_SSs

I know I'm very close to solving it, the solution has be in Index-Match combined with LookUp. But I must be doing something very silly.

Somehow I'm not that good at it. And at times when I have think for a complex situation of nesting formulae, I can mess up.

Apologies for breaking the rules of the forum and tagging you people. But I know you people are "out of the box thinkers". I have learnt so much from you people over the years.

All my intention here is that I will learn something new from you people in the process.

Please check below how I approached this problem and help.

Thanks & Regards

All Records.xlsb
	A	B	C	D	E	F
1	Biscuit	Snack		I like Biscuits	TRUE	#VALUE!
2	Sandwich	Snack		Need to buy Milk	FALSE	#VALUE!
3	Milk	Beverage		Milk is healthy	FALSE	#VALUE!
4	Potato	Vegetable			FALSE	#VALUE!
Sheet1

Cell Formulas
Range	Formula
E1:E4	E1	=ISNUMBER(FIND(A1:A4,D1))
F1:F4	F1	=INDEX($A$1:$B$4,INDEX((A1:A4),ISNUMBER(FIND($A$1:$A$4,D1,1))*1,1),2)
Press CTRL+SHIFT+ENTER to enter array formulas.
Dynamic array formulas.