return multiple results from a lookup

Heya guys,

a bit puzzled here. Perhaps I'm making things unneccesary difficult; help me.

I have a matrix-style list and I have to get the values of each result. I know that lookup only shows the first result, so it's not the answer. The index+small etc. trick doesn't seem to work either.

_________| aval1 | aval2 | aval3 | aval4 | bval1 | bval2 | bval3 | bval4 |
room 1 | x | x | | | x | | | |
room 2 | x | | | | | x | x | |

See above example:
So basicly I need a new worksheet that shows me:
room 1 | aval1 aval2 | bval1 |
room 2 | aval1 | bval2 bval3 |

Using LOOKUP I use this (but only shows the first result, and don't mind the cellreferences): =LOOKUP("x";MATRIXWORKSHEET!F7:O7;MATRIXWORKSHEET!$F$4:$O$4)


Any thoughts?

post your excel example

bumpmyhead said:

Any thoughts?

Click to expand...

Hello
Please see my attached file. Im not sure about the exact layout of your worksheet, so you may need to amend the cell references.
Be aware that the formula used is an Array Formula and after typing in/amending it, you need to end with CONTROL+SHIFT+ENTER (Not just ENTER).
This places curly brackets {} around the formula, which bestows the Array status. This key sequence is the only way to do this. Once this has been done,
and is working correctly in one cell, this can be copied/pasted to the other cells in the normal way.