Forum Discussion

SubhabM's avatar
SubhabM
Copper Contributor
Jan 29, 2025

Populate cell from horizontal assignment

I have this -

Sheet2:

The 'X' marks indicate which category the product belongs to (either Category name1, Category Name2, or Category Name3).

Sheet1:

In Column B, I want to calculate and populate the category name by first matching the product Id in Sheet 1 against the ProductId's in Sheet2, then finding the "X" in that row and then returning the Category name against the 'X'. Like this - 


Separately, I can determine the row where the ProductId is a match like this -
=MATCH($A3,'Sheet2'!A:A,0) gives 5
and also I can return the category name from the X mark like this - 
=IFERROR(INDEX('Sheet2'!$B$1:$D$1, MATCH("X", INDEX('Sheet2'!$B$5:$D$5, 0), 0)), "") gives Category Name2

But where I hard coded B$5:$D$5, I need the row to be injected from the MATCH formula above.

Is there any way to do this, or alternatives?

Many thanks in advance.

  • =IFERROR(FILTER('Sheet2'!$B$1:$D$1, INDEX('Sheet2'!$B$2:$D$1000, XMATCH($A3, 'Sheet2'!$A$2:$A$1000), 0)="X"), "")

  • =IFERROR(FILTER('Sheet2'!$B$1:$D$1, INDEX('Sheet2'!$B$2:$D$1000, XMATCH($A3, 'Sheet2'!$A$2:$A$1000), 0)="X"), "")

    • SubhabM's avatar
      SubhabM
      Copper Contributor

      This is exactly what is applicable in my case.

Resources