Results 1 to 3 of 3

Thread: Index match lookup problems

  1. #1
    Conjurer Simi's Avatar
    Join Date
    Feb 2012
    Location
    Utah, USA
    Posts
    187
    Articles
    0

    Index match lookup problems



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I am having trouble with an Index/Match look up. I am entering 2 values and to check against, then return the 3rd value from the lookup table.
    I am trying to get it so the input doesn't have to match the lookup table exactly. I have attached a woorkbook with an example.Index-Match-help.xlsx
    The table on the left which searches for depth 2 1/2, width as noted isn't working properly.

    The results I want should be as follows.
    if depth is 2 1/2 and width is <= 6 3/16 use 0.822
    if depth is 2 1/2 and width is > 6 3/16 <= 7 3/8 use .980
    if depth is 3 and width is <= 6 3/16 use 0.986
    if depth is 3 and width is > 6 3/16 <= 7 3/8 use .1.176
    Last edited by Simi; 2013-02-28 at 05:52 PM.

  2. #2
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    766
    Articles
    0
    Excel Version
    2010
    Hello Simi
    Ive spent a bit of time on your sheet, and the problems I found are:
    1. Your look-up array for the Match function is converted to text values, which can cause unexpected matching results.
    2. Also if Match is to produce accurate results where there isn't an exact match, the array has to be sorted in sequence:
    Ascending to match below the lookup value ; Descending to match above.
    So I tried various methods, and I got the best results as follows:
    1. I created an additional column in the lookup table to add together the depth and width to use in the MATCH function
    2. I sorted the table Cols (I to L) by this new numeric (Col K) im descending order instead of your ascending.
    3. I then adjusted the array formulae to take into account the new column.

    The results aren't bad. All those you have stipulated above are correct, but there are others outside your examples and a few at the end
    where the data values were beyond those in the table (#N/A). I realise that it probably isn't the final result, but I hope it helps you to get there.

    Hercules
    Attached Files Attached Files

  3. #3
    Conjurer Simi's Avatar
    Join Date
    Feb 2012
    Location
    Utah, USA
    Posts
    187
    Articles
    0
    Hercules, thank you for your input. That seems to be on the right track. I may end up limiting the data that can be entered into the cell so I only get exact matches. This has been a pain.

    Simi

Posting Permissions

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