Consulting

Results 1 to 11 of 11

Thread: Lookup value in table with two criteria in rows

  1. #1
    VBAX Contributor
    Joined
    Mar 2009
    Location
    Porto, Portugal
    Posts
    180
    Location

    Lookup value in table with two criteria in rows

    Hi
    I'm stucked with this issue today. Maybe it's the new years's eve hangover still.

    In the attached file I have 2 sheets.
    One has data with 5 columns for each month.
    What I intend to do in the other sheet is to lookup for values in the first sheet related to the month I choose.

    I have tried some combinations of lookup, match and index functions but I always get errors.

    I would appreciate your help

    Thank You very much
    Attached Files Attached Files

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,247
    Location
    What are G, Gd, GC and so on?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    VBAX Contributor
    Joined
    Mar 2009
    Location
    Porto, Portugal
    Posts
    180
    Location
    Quote Originally Posted by xld View Post
    What are G, Gd, GC and so on?
    Sorry
    It's Task1, Task2,..., Task5

    I forgot to substitute them.

  4. #4
    VBAX Contributor
    Joined
    Mar 2009
    Location
    Porto, Portugal
    Posts
    180
    Location
    Here's the corrected file
    I'm still trying index and match functions but can't figure the error.

    Some help would be nice
    Thanks in advance
    Attached Files Attached Files

  5. #5
    lookuptest.xlsx

    Try this formula for Cell B6, Sheet "Mensal":

    ={INDEX(Folha2!$C$4:$BJ$99,0,MATCH(1,(Folha2!$C$2:$BJ$2=Mensal!$B$3)*(Folha2!$C$3:$BJ$3=Mensal!B$5),0))}

    I added the 0, and the $ (bold in above formula).

    NH

  6. #6
    VBAX Contributor
    Joined
    Mar 2009
    Location
    Porto, Portugal
    Posts
    180
    Location
    Quote Originally Posted by Nathan_Hale View Post
    lookuptest.xlsx

    Try this formula for Cell B6, Sheet "Mensal":

    ={INDEX(Folha2!$C$4:$BJ$99,0,MATCH(1,(Folha2!$C$2:$BJ$2=Mensal!$B$3)*(Folha2!$C$3:$BJ$3=Mensal!B$5),0))}

    I added the 0, and the $ (bold in above formula).

    NH
    Hi Nathan
    Thank you for your help but formula don't return any result.
    In the file you uploaded there's nothing neither.
    Is there something missing?

    Ioncila

  7. #7
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,247
    Location
    Try this array formula

    =INDEX(Folha2!$C$4:$BJ$99,ROW($A1),MATCH(1,(Folha2!$C$2:$BJ$2=Mensal!$B$3)* (Folha2!$C$3:$BJ$3=Mensal!B$5),0))
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  8. #8
    VBAX Contributor
    Joined
    Mar 2009
    Location
    Porto, Portugal
    Posts
    180
    Location
    Works perfectly
    Thank you very much once again

    There's a formula detail that I can't figure (the reference to ROW($A1)) but I'm going to search and study it.

    Regards
    Ioncila

  9. #9
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,247
    Location
    It just gives you a row index into the INDEXed range, starting at 1, and incrementing as you copy down.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  10. #10
    VBAX Contributor
    Joined
    Mar 2009
    Location
    Porto, Portugal
    Posts
    180
    Location
    Quote Originally Posted by xld View Post
    It just gives you a row index into the INDEXed range, starting at 1, and incrementing as you copy down.
    Thanks again
    Ioncila

  11. #11
    Quote Originally Posted by ioncila View Post
    Hi Nathan
    Thank you for your help but formula don't return any result.
    In the file you uploaded there's nothing neither.
    Is there something missing?

    Ioncila

    Hi Ioncila,

    Glad you figured it out. Sorry about the attachment. I am just getting started on this site. When I used the formula I posted it returned no result as well.... until I changed the month in the drop-down and then it showed a result. I didn't think there was a number to return for the default month. Anyway glad it worked out.

    NH

Posting Permissions

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