Categorizing Variable using List

Hi, I read one of the thread on how actually do you do this.

What im trying to do is using a set of list (data dictionary) to group data. Reason using list is so I can expand the list in future.

I manage to use this but I want to slightly improve it.

Please Login or Register  to view this content.

If you look at the attached example, instead of the formula giving me the column E result i want it to give the column F result.


Your feedback is most welcome.

deleted, redundant

In an INDEX() formula, the first range is the values you are pulling a result from. The second parameter resolves to a numeric "position" to pull from that first range.

So to make your existing formula pull back from column F, just change the first range to that:

=INDEX($F$2:$F$6,MATCH(TRUE,INDEX(ISNUMBER(SEARCH($E$2:$E$6,A2)),),0))


Now, to make that formula dynamic and work with any size data table in E:F, adjust it like so:

=INDEX($F:$F, MATCH(TRUE, INDEX(ISNUMBER(SEARCH($E:$E,A2)),),0))