0

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?

Ushay
  • 95
  • 7

3 Answers3

1

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

enter image description here

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

Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
  • Thanks for the formula and solution. This solution appelas to me neat and short but unfortunately I will not be able to apply this to my sheet. Actually the data I have is generated through FILTER Formula and the Table cannot be inserted onto this data, as that leads to SPILL error. – Ushay Sep 06 '21 at 14:36
  • @Ushay since you gave no examples of how your sheets are set up, I cannot give you more advice. However, it should not be difficult to adapt this concept to your actual data. – Ron Rosenfeld Sep 06 '21 at 15:33
  • I did some tuning in my data sheet to apply your formula and it works well. Thanks – Ushay Sep 07 '21 at 05:00
0

May be misunderstanding. But when referencing the cell address with indirect have you written it like so?

=INDIRECT(B2) or =INDIRECT("B2")

Tom_Foolery
  • 1
  • 1
  • This formula will work if `B2` contains `Fruits` or `Vegetables` but op has `Apple` in `B2` cell and he wants group name based on B2 cell content. – Harun24hr Sep 06 '21 at 10:06
  • Could you not create a reference table then and just do a lookup of what is in cell "B2" into the reference table. – Tom_Foolery Sep 06 '21 at 10:09
  • @Tom_Foolery It would work, but I want to use the existing Name definitions – Ushay Sep 06 '21 at 10:42
0

If you many Named defined then this will be impractical. But you have only two names or few more then you can try below formula. Here Fruits named range is A1:A3 and Vegetables named range is B1:B3.

=IF(SUM(--(Fruits=D1))>0,"Fruits",IF(SUM(--(Vegetables=D1))>0,"Vegetables","No Group"))

You may need array entry with CTRL+SHIFT+ENTER for old versions of excel.

enter image description here

Harun24hr
  • 30,391
  • 4
  • 21
  • 36