+ Reply to Thread
Results 1 to 6 of 6

Match function looking up number value

  1. #1
    Registered User
    Join Date
    09-13-2012
    Location
    Munich Germany
    MS-Off Ver
    Microsoft office Home and Student 2016
    Posts
    14

    Match function looking up number value

    Hello Everyone,

    I've been searching for an answer on this for a while today but have yet to find a fix. the arrays and lookup are formatted at number but I'm still have an NA error.

    Attached is my workbook, the formula is in sheet “KALK column AI. I’m trying to match the value in AH in the table in sheet “ELEV” column “H”. Both columns are in number format, but only 0 is recognized.

    What am I doing wrong? This is a bit embarrassing.

    Thanks in advance.
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,323

    Re: Match function looking up number value

    Are you still using Excel 2007? If not, please update your forum profile.

    Your lookup table is in descending order, so try -1 as the lookup order:

    =INDEX(Table1[EBENE],MATCH(KALK!AH2,Table1[H],-1))
    Last edited by AliGW; 01-18-2021 at 10:32 AM.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    09-13-2012
    Location
    Munich Germany
    MS-Off Ver
    Microsoft office Home and Student 2016
    Posts
    14

    Re: Match function looking up number value

    Hello AliGW,

    Thanks for the quick reply. I'm using Microsoft office for home and Student 2016. I will change that in my profile. I updated the formula with -1 and it kind of worked as it's now finding the number, but it's not giving the corresponding indexed Column. doesn't 0 give an exact match?
    attached is the modified workbook
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    09-13-2012
    Location
    Munich Germany
    MS-Off Ver
    Microsoft office Home and Student 2016
    Posts
    14

    Re: Match function looking up number value

    Quote Originally Posted by AliGW View Post
    Are you still using Excel 2007? If not, please update your forum profile.

    Your lookup table is in descending order, so try -1 as the lookup order:

    =INDEX(Table1[EBENE],MATCH(KALK!AH2,Table1[H],-1))
    I updated my profile to my current excel version. I would like to retrieve the exact value for the match type. so I'm not sure how -1 will work for the solution.

    Regards,
    David

  5. #5
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Match function looking up number value

    It looks like a case of floating point errors (more than you want to know about floating point errors: https://www.excelforum.com/groups/ma...nd-errors.html ).

    I tested by putting =(KALK!AH19-ELEV!C18) [note the "unnecessary" parentheses] into KALK!AJ19 and formatting as scientific. Similar formulas show that most of the values in AH are slightly different from the corresponding values in ELEV column C. Tracing the source of the error, and it appears that most of the values in Wisext column AA are slightly different from the displayed/expected value.

    I'm not sure where you would want to fix this. I edited the formula in ELEV column C to =ROUND(current formula,3) to force the values to be exact to 3 decimal places (the same as you were rounding in KALK). This seemed to "fix" the formula in KALK. There's probably some value in looking at the source of the values in Wisext to see if there is a better place to resolve the floating point errors. One way or another, the problem is floating point error and the solution is to make sure that the problem values/formulas properly account for the errors inherent in floating point arithmetic.
    I would like to retrieve the exact value for the match type. so I'm not sure how -1 will work for the solution.
    If you fix the floating point error, exact match or approximate match (descending) should not make a difference. The advantage of using -1 is that the lookup will be more efficient which is important when your lookup table becomes large. The advantage for using 0 is that you will get an error value (N/A or something) instead of a wrong number when the numbers don't match exactly (preventing something like this from flying under the radar). Your choice how you want to program it.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  6. #6
    Registered User
    Join Date
    09-13-2012
    Location
    Munich Germany
    MS-Off Ver
    Microsoft office Home and Student 2016
    Posts
    14

    Re: Match function looking up number value

    Hello MrShorty,
    Thank you for your explanation and help. Sorry for the late reply. Before your reply I also tooled around with the round function but overlooked the result so I didn't notice that the problem was fixed until a couple hours later. What helped most is the term “floating point errors “. I ran into this problem earlier while developing my workbook but didn’t have a name for it.

    All in all, I appreciate both your guys help very much.

    This community is quick and always helpful.

    Have a good rest of week!


    David

+ 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] How to Return Row Number From Match Function
    By EdwardSnowden in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 08-08-2019, 03:28 PM
  2. how to use match function to match that number and return a value?
    By eddie01001 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-05-2019, 03:55 PM
  3. [SOLVED] Function to Match the number in given range
    By Karnik in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-26-2018, 08:42 AM
  4. [SOLVED] could not find column number by using match function
    By shiva_raj in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-09-2017, 11:12 AM
  5. Replies: 6
    Last Post: 03-17-2014, 08:10 PM
  6. number not recognised by Match function
    By st!ngray in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 01-28-2013, 05:57 AM
  7. MATCH-function based on column number
    By mkvassh in forum Excel General
    Replies: 4
    Last Post: 10-07-2010, 10:32 AM

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