Eric Kindberg

Q: Need a formula that uses vlookup & hlookup to return a single value

I am creating a pricing worksheet with multiple values. In the table below if I select size 16 in Blue the result should return 19.5

 

RedYellowBlueGreen
size
1056.257.58.75
121314.2515.516.75
141516.2517.518.75
161718.2519.520.75
201920.2521.522.75

iPad Air, iOS 9.3.5

Posted on Sep 24, 2016 6:04 AM

Close

Q: Need a formula that uses vlookup & hlookup to return a single value

  • All replies
  • Helpful answers

  • by Barry,Solvedanswer

    Barry Barry Sep 27, 2016 4:26 PM in response to Eric Kindberg
    Level 7 (32,502 points)
    iWork
    Sep 27, 2016 4:26 PM in response to Eric Kindberg

    Hi Eric,

     

    Screen Shot 2016-09-24 at 11.58.49 AM.png

    Larger table is Table 1.

    Formula is in C2 of Table 2.

     

    C2: =INDEX(Table 1::A1:E7,MATCH(A,Table 1::A,0),MATCH(B2,Table 1::1:1,0),area-index)

     

    Regards,

    Barry

  • by Eric Kindberg,

    Eric Kindberg Eric Kindberg Sep 27, 2016 4:31 PM in response to Barry
    Level 1 (4 points)
    iWork
    Sep 27, 2016 4:31 PM in response to Barry

    Thanks Barry,

     

    Exactly what l needed. Had to play with it to understand it. Once l did-perfect solution.

     

    I'm looking for a book or website to learn what you know. Can you recommend something?

     

    Eric

  • by Barry,

    Barry Barry Sep 27, 2016 7:03 PM in response to Eric Kindberg
    Level 7 (32,502 points)
    iWork
    Sep 27, 2016 7:03 PM in response to Eric Kindberg

    Hi Eric,

     

    Most of what I know about spreadsheets and formulas has come from the manuals accompanying Numbers or its predecessors going back to AppleWorks and ClarisWorks, and from occasional brushes with Microsoft Works, MS Excel and various other spreadsheets.

     

    One of the most useful of these was the iWork 09 Formulas and Functions User Guide, which gave a description, the syntax and at least one example of where/how the function could be put to use for every function supported in that version of Numbers. The manual, most of which also applies to Numbers 3.6 (and likely is useful for Numbers 4 as well) is still available via download from Apple - Support - Manuals. Although no similar manua was published for the current version(s) of Numbers, the Mac application does contain the Function Browser (which opens in the right sidebar whenever you type = in a cell) contains the same information in much the same format.

     

    The browser is searchable by the name of each function or set of similar functions. Click on the "Reference" item, and you'll find the LOOKUP function, INDEX, MATCH, ROW() and COLUMN(), all useful for many of the questions of the type you asked here.

     

    The other source of information for me has been this forum (and its earlier incarnations), and the people who post here.

    I read most of the posts in this community, and usually read the responses to the questions. For many questions, I'll learn by opening a new spreadsheet and attempting to solve the issue under discussion. Whether I'm successful or not, I usually learn something from the experience and/or from reading (and sometimes trying) solution offered by others.

     

    Regards,

    Barry