Search code examples
arraysexcelexcel-formulareference

How to make the array in a formula dynamic?

enter image description here

I would like to get the category for the names in Sheet2. The categories are part of the headers in a table of Sheet1.

Because table in Sheet1 is structured like N/Y under each column to indicate the category (for example, dog is under Animal category, so Y is only under the column with header Animal), my approach is to search the index on the row of the name. To find the row number, match() function can easily get the job done.

However, I am stuck in the step of putting the resultant row number into a match() function to get the column number of Y under one of the category.

Are there any ways that can make it like =match(row_i, "Y",0)? where row_i is like, match("dog", A:A,0)

Thank you so much!

Having searched online, all the results are just dynamic arrays which are not relevant...

Solution

  • Alternative solution, no LAMBDA():

    enter image description here

    Formula in F2:

    =INDEX(TOCOL(IFS(B2:D7="Y",B1:D1),3),XMATCH(F2:F6,A2:A7))