Results 1 to 3 of 3

Thread: return multiple results from a lookup

  1. #1

    return multiple results from a lookup

    Register for a FREE account, and/
    or Log in to avoid these ads!

    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?

  2. #2
    Acolyte Jean Paul's Avatar
    Join Date
    May 2015
    Excel Version
    post your excel example

  3. #3
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    York, England
    Excel Version
    Quote Originally Posted by bumpmyhead View Post

    Any thoughts?
    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.
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts