+ Reply to Thread
Results 1 to 3 of 3

Table Lookup/Index Match Help

  1. #1
    Registered User
    Join Date
    01-13-2014
    Location
    Nowhere, KS
    MS-Off Ver
    Excel 2007
    Posts
    7

    Table Lookup/Index Match Help

    I have a table with air volume in a row across the top, load down the left-most column, and vane position data corresponding to air volume and load in the middle. I would like to be able to specify an air volume (top row) and a vane position (within the table) and have a formula use the table to determine the corresponding load (left column). For both the air volume and vane position, I don't want exact matches but the nearest lower value. I can't quite get my head around how to use the Index Match functions to do this. I know how to use it to get a vane position value from the table if the air volume and load are known, but not how to do the reverse. As an example see the attached spreadsheet where I'm looking for the formula to put in cell B21 so that for an air volume of 13.8 and vane position of 57, it will return a load value of 725. Please help!
    Attached Files Attached Files

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,737

    Re: Table Lookup/Index Match Help

    Put this formula in B21:

    =INDEX(B3:B16,MATCH(B20,INDEX(C3:K16,0,MATCH(B19,C2:K2))))

    Hope this helps.

    Pete

  3. #3
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Table Lookup/Index Match Help

    See the green cell in the attached file.

    Please reply.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Lookup, match and index to return percentage to table to draw a radar chart
    By smudgepost in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-26-2014, 11:31 AM
  2. Replies: 6
    Last Post: 04-30-2014, 02:42 AM
  3. Replies: 2
    Last Post: 04-28-2013, 05:55 PM
  4. [SOLVED] Need help using Index and Match for table lookup with 2 criteria matching
    By lorne17 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-27-2012, 09:26 AM
  5. [SOLVED] Using LOOKUP, MATCH, INDEX to obtain cell value from data table
    By rfernandes in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 09-05-2012, 05:31 PM
  6. Dependent Percentile Formula in Table with Nested Lookup or Index Match?
    By chogan in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 11-11-2010, 06:34 PM
  7. table, index, array, match, lookup?
    By spxer in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-08-2006, 04:35 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1