Forum Discussion
SubhabM
Jan 29, 2025Copper Contributor
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"), "")
- SubhabMCopper Contributor
This is exactly what is applicable in my case.