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
| Red | Yellow | Blue | Green | |
| size | ||||
| 10 | 5 | 6.25 | 7.5 | 8.75 |
| 12 | 13 | 14.25 | 15.5 | 16.75 |
| 14 | 15 | 16.25 | 17.5 | 18.75 |
| 16 | 17 | 18.25 | 19.5 | 20.75 |
| 20 | 19 | 20.25 | 21.5 | 22.75 |
iPad Air, iOS 9.3.5
Hi Eric,
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
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
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
Need a formula that uses vlookup & hlookup to return a single value
