Search that returns Range
Results 1 to 3 of 3

Thread: Search that returns Range
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Nov 2010
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Search that returns Range

    Using Excel for Mac 2011...
    Col A Col B Col C
    val 1 val 9
    val 1 val 10
    val 1 val 4
    val 2 val 4
    val 2 val 5
    val 3 val 6
    val 3 val 4 desired look-up cell
    val 3 val 8

    trying to look up the value as indicated in Column C from a different worksheet tab, knowing only the values in Col A and Col B. Not sure how to do this as the Vlookup only gives the the row with "val 3" - "val 6".

    Alternatively, if there was a search that returned the row range for "val 3", I think I'd be able to search in that range for "val 4" and find the row I need for Col C.

    Thanks!!!!
    Last edited by Frank Horatio; Apr 27th, 2012 at 03:59 PM.

  2. #2
    MrExcel MVP DonkeyOte's Avatar
    Join Date
    Sep 2002
    Location
    Suffolk, UK
    Posts
    9,118
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Search that returns Range

    A number of alternative methods, in the below I have simplified the example with a single sheet.

    Sheet1

     ABCDEFGH
    1Col ACol BCol C  Col ACol BCol C
    2val 1val 9b  val 1val 10a
    3val 1val 10a  val 1val 9b
    4val 1val 4missing  val 2val 51
    5val 2val 42  val 2val 42
    6val 2val 51  val 3val 13
    7val 3val 6y  val 3val 8x
    8val 3val 4missing  val 3val 6y
    9val 3val 8x  val 3val 127

    Spreadsheet Formulas
    CellFormula
    C2=IFERROR(INDEX($H$2:$H$9,MATCH(A2&"@"&B2,INDEX($F$2:$F$9&"@"&$G$2:$G$9,0),0)),"missing")


    Excel tables to the web >> Excel Jeanie HTML 4

    Generally speaking it's often best to create unique keys (concatenating values of interest) such that your search operates on a single column.

    The embedded use of INDEX is merely to avoid Array entry requirement - a number here would advise against it.

    If you are retrieving values, they were numeric type and unique match then you could use SUMIFS (given use of XL2011)
    Does my a$$ look big in this picture ?

  3. #3
    New Member
    Join Date
    Nov 2010
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Search that returns Range

    Thanks!

    Unfortunately, I could not control the reference worksheet tab in this instance. The formula works great though...

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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