Function to classify/consolidate list

[uhtt]

Hi all,

I've been trying to get a function to work all day today and I didn't get very far.

I was wondering if there is a way to consolidate data from an input to be renamed to an output by referencing it from another list.

I've left what I was working on at work but I was playing with IF statements, trying to use MATCH, and about 4 other ways. I could never seem to get the list to work off a reference list, mainly because I don't think IF statements like using A1:A10 selection. I did get something working by manually typing each 'class', but it would make it hard to update it as required.

I'll post what I did last night, but if someone could have a look at my excel, it shows my input and desired output as well as a reference list.

Cheers

[benishiryo]

hi uhtt, welcome to the forum. is it possible you input your table like i did in M6:N14? then a simple VLOOKUP like i did in column G will do. but if you have difficulties doing that, then use this array formula in F7 & copy down:
=INDEX($H$6:$K$6,SMALL(IF($H$7:$K$9=D7,COLUMN($H$7:$K$9)-COLUMN($H$7)+1),1))

...confirmed by pressing CTRL-SHIFT-ENTER to activate the array. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL-SHIFT-ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.