Extract Group Name based on the Cell content

I have a set of data grouped and assigned 'Names' based on their categories which is something like Fruits: Apple, Orange, Kiwi.. and Vegetables: Tomato, Onion, Cabbage... Here Apple, Orange, Kiwi are defined with a Name called Fruits and similarly Tomato, Onion, Cabbage with a Name Vegetables.

In my sheet I have the cells Apple, Tomato etc., and would like to extract the Name assigned to it next to them. I used =INDIRECT(Cell Address), unfortunately did not work. Is there any formula for this?

Especially if you want to avoid lengthy formulas and VBA, I suggest re-arranging your data a bit.

• Put the Name of the category in Row 1
• Format as Table and Name the Table something. (I Named it Foods)

Then your formula becomes simple:

=INDEX(Foods[#Headers],1,MAX((ISNUMBER(SEARCH(D33,Foods))*COLUMN(Foods))))

Where D33 contains the name of the food

You could use a regular range and addressing, but using a table makes the names and ranges dynamic