+ Reply to Thread
Results 1 to 5 of 5

Help with a two-dimensional lookup/INDEX function, please!

Hybrid View

  1. #1
    Registered User
    Join Date
    10-28-2013
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    3

    Help with a two-dimensional lookup/INDEX function, please!

    Have become so frustrated by this that I've actually signed up. Despite having searched for a solution and found varying ideas which all appear to work for those that suggested them, I haven't been able to replicate the same working formula. I've even downloaded a document with a series of lookup functions implemented but even then I'm not getting it to work for me.

    I have a reference table of values. The ROWS axis features numbers incremented in 0.1 instances from 0.5 through to 2.5. The COLUMNS axis features numbers incremented in 5 instances from 5 to 40. The references between are a series of numbers associated with these values. They needn't mean anything but looking up 0.9 and 25 would give me 20 as a result. Unfortunately, when I use an Index function to look these up, I'm told there's an error in my formula.

    =INDEX(Table, MATCH(X26, W2:W22,0), MATCH(X27,X1:AE1,0))

    'Table' is my namespace for the entire range of my lookup table. X26 is the cell containing "0.9", as an example, and X27 is the cell containing "25", as an example. W2:W22 is the ROWS axis label, X1:AE1 is the COLUMNS axis header.

    What am I doing wrong? Appreciation in advance, thanks .

  2. #2
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Help with a two-dimensional lookup/INDEX function, please!

    Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Registered User
    Join Date
    10-28-2013
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Help with a two-dimensional lookup/INDEX function, please!

    example problematic table.xlsx

    Example attached, thanks.

  4. #4
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Help with a two-dimensional lookup/INDEX function, please!

    your lookup refers to a different range than the defined name "table"
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    10-28-2013
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Help with a two-dimensional lookup/INDEX function, please!

    Thank you.

+ 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. [SOLVED] Three dimensional lookup???
    By rajncajn in forum Excel Formulas & Functions
    Replies: 22
    Last Post: 05-31-2012, 12:53 PM
  2. Two-dimensional lookup
    By ben803 in forum Excel General
    Replies: 2
    Last Post: 02-09-2011, 06:12 PM
  3. 2 dimensional lookup
    By vamshi57 in forum Excel General
    Replies: 8
    Last Post: 01-09-2011, 07:11 AM
  4. Two Dimensional Lookup
    By Swastik Banerje in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-19-2009, 12:57 PM
  5. [SOLVED] Two dimensional lookup
    By CDog in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-24-2005, 04:00 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