I have a table of item values with a category of color In row 1 with color codes in the column that correspond to that category. Ex column A is red and in cell A20 is color code 221. But there a some color codes where 221 is red but 222(a slightly more darker tone) is under the brown column even though I would classify it as a red.

The table I have now has color categories in A1:L1 color codes from A2:L67 but most of those cells are blank. Column G has 66 values while Column F only has 23. If that’s going to play a factor.

For ease of sorting these items i would like to use lookup or match to enter in a color code and it give what color category it belongs too. So if I enter color code 321 it would give Brown as the color category. And id rather not just do control F and typing the code to see what column it’s in

I have watched a few videos on v v lookup and I have tried transposing the table to correspond to the video. But since my idea calls for a variable column index number based on what cell value I give it doesn’t work as I intend. So I’m stuck with that aspect.

CodePudding user response：

You can try FILTER() with MMULT() function.

=FILTER(A1:B1,MMULT(SEQUENCE(1,ROWS(A2:B6),,0),--(A2:B6=A13)))

Another approach could be using BYCOL() function.

=FILTER(A1:B1,BYCOL(A2:B6,LAMBDA(col,SUM(--(col=A13)))))

Extend range in your actual case.

CodePudding user response：

Let's say your data it's like this:

The formula to locate the color is:

=INDEX($A$1:$C$1;1;SUMPRODUCT(--($A$2:$C$6=A11)*COLUMN($A$1:$C$1)))

This will work only if each id in colors dataset is unique.