Page 1 of 2 12 LastLast
Results 1 to 10 of 11

lookup table using Horizontal and Vertical criteria

This is a discussion on lookup table using Horizontal and Vertical criteria within the Excel Questions forums, part of the Question Forums category; With the example table below, i want to put the appropriate value in C3 up to C6 if the criteria ...

  1. #1
    Board Regular
    Join Date
    Oct 2005
    Posts
    344

    Default lookup table using Horizontal and Vertical criteria

    With the example table below, i want to put the appropriate value in C3 up to C6 if the criteria is met in the right table. The criteria is : it will match the value of A3 Ex in the right table then once it is located it will use the column "Up" or "Low" depends on the value of B3. Example: the value of C3 should be 4%, C4 will be 4%, C5 is 2% and C6 is 5%. help please

    =====================================

    ******** ******************** ************************************************************************>
    Microsoft Excel - Book1___Running: 12.0 : OS = Windows XP
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    E
    F
    G
    H
    2
    RateCriteriaPercentage RateUpLow
    3
    EXLow Ex5%4%
    4
    VGUp VG4%3%
    5
    GUp G3%2%
    6
    EXUp F2%1%
    Sheet1

    [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

  2. #2
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    32,297

    Default Re: lookup table using Horizontal and Vertical criteria

    =index($g$3:$h$6,match(a3,$f$3:#f#6,0),match(b3,$f$2:$h$2,0))
    Want better/faster responses to your questions?
    Use Excel Jeanie to post samples of your sheet.

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  3. #3
    MrExcel MVP
    Int'l Moderator
    Greg Truby's Avatar
    Join Date
    Jun 2002
    Location
    39 17' 15" N, -94 40' 26" W
    Posts
    9,770

    Default Re: lookup table using Horizontal and Vertical criteria

    Quote Originally Posted by jonmo1 View Post
    =index($g$3:$h$6,match(a3,$f$3:#f#6,0),match(b3,$f$2:$h$2,0))
    I reckon jonmo's fingers are still warmin' up... edit the "#" signs to be "$" signs and you should be good to go.

    =index($g$3:$h$6,match(a3,$f$3:$f$6,0),match(b3,$f$2:$h$2,0))
    Greg

    Work: XL 2003, 2007 and 2010 on Windows 7
    Please use CODE tags - especially for longer excerpts of code.

  4. #4
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    32,297

    Default Re: lookup table using Horizontal and Vertical criteria

    Doh!!!

    Want better/faster responses to your questions?
    Use Excel Jeanie to post samples of your sheet.

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  5. #5
    Board Regular
    Join Date
    Oct 2005
    Posts
    344

    Default Re: lookup table using Horizontal and Vertical criteria

    Sir Why it has #Ref! error...thanks

  6. #6
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    32,297

    Default Re: lookup table using Horizontal and Vertical criteria

    See the typo Mr. Truby pointed out...
    =index($g$3:$h$6,match(a3,$f$3:#f#6,0),match(b3,$f$2:$h$2,0))
    SHOULD BE
    =index($g$3:$h$6,match(a3,$f$3:$f$6,0),match(b3,$f$2:$h$2,0))
    Want better/faster responses to your questions?
    Use Excel Jeanie to post samples of your sheet.

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  7. #7
    MrExcel MVP
    Int'l Moderator
    Greg Truby's Avatar
    Join Date
    Jun 2002
    Location
    39 17' 15" N, -94 40' 26" W
    Posts
    9,770

    Default Re: lookup table using Horizontal and Vertical criteria

    Did you see the comment on the needed edit? Did you change the "#" signs to "$" signs and you are still getting a #REF error?
    Greg

    Work: XL 2003, 2007 and 2010 on Windows 7
    Please use CODE tags - especially for longer excerpts of code.

  8. #8
    Board Regular
    Join Date
    Oct 2005
    Posts
    344

    Default Re: lookup table using Horizontal and Vertical criteria

    yes sir i copy the correct one and it is still has #Ref!

    =index($g$3:$h$6,match(a3,$f$3:$f$6,0),match(b3,$f$2:$h$2,0))

  9. #9
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    32,297

    Default Re: lookup table using Horizontal and Vertical criteria

    Ahh, found another problem...Sheesh is it that early??
    I guess this is what I get for not testing...

    =index($g$3:$h$6,match(a3,$f$3:$f$6,0),match(b3,$f$2:$h$2,0))
    should be..
    =INDEX($G$3:$H$6,MATCH(A3,$F$3:$F$6,0),MATCH(B3,$G$2:$H$2,0))
    Want better/faster responses to your questions?
    Use Excel Jeanie to post samples of your sheet.

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  10. #10
    MrExcel MVP
    Int'l Moderator
    Greg Truby's Avatar
    Join Date
    Jun 2002
    Location
    39 17' 15" N, -94 40' 26" W
    Posts
    9,770

    Default Re: lookup table using Horizontal and Vertical criteria

    Well, I had to put together a test sheet and get the #REF! error myself before I figured it out. And yes that is the key edit to make, change the "F" to a "G" in the second MATCH():

    =INDEX($G$3:$H$6,MATCH(A3,$F$3:$F$6,0),MATCH(B3,$G$2:$H$2,0))
    Greg

    Work: XL 2003, 2007 and 2010 on Windows 7
    Please use CODE tags - especially for longer excerpts of code.

Page 1 of 2 12 LastLast

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
  •  


DMCA.com