0

I have a series of named ranges on a tab stored with the top row serving as each range's name (eg fruit, apple, pear, apricot). There are empty columns between each list.

On a different tab I have a column "Description" which has data values that correspond to some of those items (apricot, apricot, apple, pear). An adjacent column "Category" reads the Description cell and matches the contents to the appropriate range. Currently that formula reads 

=IF(Countif(Fruit, [@Description])=1, "Fruit", if(countif(Fish, [@Description])=1, "Fish",...))

The formula gets the job done, but it's ugly and does not scale very easily. Is there a cleaner, more elegant formula I could use?

Currently using Excel 2013

CSchulz
  • 10,882
  • 11
  • 60
  • 114
Jason
  • 1
  • 1
  • Making it scalable may make it a little more complicated. I'd look into building an array which stores your data, say the named ranges. You can populate this yourself in the macro, or make a dynamic array which expands based on a macro populating it. You can then add the description to the array also, if it was 2D. Or have a macro loop through the array and return the description. So many possible options, start with baby steps and build on it. – Shauno_88 Feb 25 '15 at 22:51

1 Answers1

0

I don't think there's other way to achieve it but you can include;

IFERROR(COUNTIF.....)
O'Brien
  • 43
  • 1
  • 1
  • 11