Match-Index with variable column

[GLedesma26]

Hi there,

I have had this challenge for a while that I am not able to overcome. I have a long database of articles to which I want to give a number. Each article (row) has a Category and a Subcategory (2 columns in the table). To give a number to the Category was easy by using the INDEX&MATCH functions (instead of VLOOKUP). What I did was create a little table with all my categories and a number next to them, nothing exotic.
Now, my subcategories are dependant on the Category (that is, each Categroy has different Subcategories), and I want to give numbers to the subcategories, always going from 1 to X for each Category.
I thought I could do something similar than my previous step, but this INDEX&MATCH or the VLOOKUP functions don't allow you to look for something in array and indicate in which column they should look for the item.

Can someone try to give me a hand?

[#2]

@GLedesma26 

Could you attach a sample workbook (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Alternatively, you can attach it to a private message to me. Thanks in advance.

[#3]

Hi @Hans Vogelaar 
https://docs.google.com/spreadsheets/d/1WEo43g1xBpRYPhPwu_SEKJQd4e3ryppfXbh6u0Pl-oI/edit#gid=0
Here I created a google sheet with an example of the case.
Of course, in reality, I will have many more categories and subcategories, and even some of them might have the same name and yet get a different number.
You should be able to make comments on the sheet. Thank you in advance!!!

[#4 · Accepted solution]

@GLedesma26 Entered this formula in E2 and copied it down. It seems to do what you need.

=INDEX($H$13:$H$15,MATCH(C3,INDEX($I$13:$K$15,,MATCH(B3,$I$12:$K$12,0)),0))

File attached.

[#5]

@GLedesma26 

=INDEX($H$13:$H$15,SUMPRODUCT(MMULT(TRANSPOSE(N(C3=$I$13:$K$15)),ROW($1:$3))))

An alternative could be INDEX and SUMPRODUCT. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.