Lookup in matrix with only horizontal values

[henzo]

Hi,

i hope somebody can help me out. I've created a small matrix that shows different types of chairs and the colors they are available in.

i would like to execute a lookup in the matrix so i immideatly now the available colors without having to go through the matrix myself. i've tried 2-dimensional lookup, but nothing seems to work...

attached a very simplified version of the matrix.

any help is welcome!

[6StringJazzer]

The matrix is clear, but this is not:

i immideatly now the available colors without having to go through the matrix myself

What does that mean? Can you modify your file to give an example of what you want the result to look like?

[henzo]

Hi,

i've made some changes to the file. In the overviewsheet i would like to see all the different colors per chair without having to look in the matrixsheet.

for example for chair 1 i would like to see in cell B5 'Blue" and in C5 'green'.

for chair 2 i would like to see in cell B6 'Blue", in cell C6 'orange' and in cell D6'gray'


hope this is more or less clear?

thanks!

[ChemistB]

You need an arrayed formula in Overview!B5 copied across and down

=IFERROR(INDEX(Matrix!$B$1:$K$1, SMALL(IF(Matrix!$B2:$K2="x", COLUMN(Matrix!$B$1:$K$1)-1),COLUMNS($A$1:A$1))),"")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
Does that work for you?

[azumi]

Maybe this should be workin, but pls have a look at the file attach

Cheers