+ Reply to Thread
Results 1 to 6 of 6

Index closest match

  1. #1
    Registered User
    Join Date
    11-05-2004
    Location
    Palm Springs California
    MS-Off Ver
    Office 2011 for Mac
    Posts
    37

    Index closest match

    My Index cells reside in AN20:BP20
    and contained the following data: -100 -90 -80 -70 -60 -50 -40 -30 -20 -10 -8 -7 -6 -5 -4 -3 -2 -1 0 10 20 30 40 50 60 70 80 90 100

    The MATCH cell is BE18 and is a merged cell group of 3 cells: BE18:BI18
    these cells currently contain a single calculated data point of -32%

    The RESULT cell is AM22 and contains the following: =INDEX(AN20:BP20,MATCH(MIN(ABS(AN20:BP20-BE18)),ABS(AN20:BP20-BE18),0))
    This formula is entered as an array i.e. Shift/Control/Enter

    My problem is the returned value is alway zero (0)?

    I see one potential problem and that is the absolute qualification within the formula. I have tried removing the ABS's and the resulting formula just returns an error message.

    As the data indicates I obviously need to be able to differentiate between positive and negative numbers.

    Can someone please point out the error of my ways?

    Thanks, Michael

  2. #2
    Valued Forum Contributor
    Join Date
    05-07-2012
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    354

    Re: Index closest match

    -32% is actually -0.32. So 0 is the right answer.

    try changing -32% to just -32. With your equation - just the way it is - you get the right answer.
    Regards,
    Vandan

  3. #3
    Registered User
    Join Date
    11-05-2004
    Location
    Palm Springs California
    MS-Off Ver
    Office 2011 for Mac
    Posts
    37

    Re: Index closest match

    Changed the cell that the formula matches too and it now is outputting "-.32" but the formula is still outputting zero?

    Thanks, Michael

  4. #4
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,366

    Re: Index closest match

    maybe you should post the problem workbook.
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir

  5. #5
    Registered User
    Join Date
    11-05-2004
    Location
    Palm Springs California
    MS-Off Ver
    Office 2011 for Mac
    Posts
    37

    Re: Index closest match

    I am still working through this issue but seem to be making some progress.

    I needed to change the array data as well as the target. ;√)

    Thanks to everyone who made suggestions.

    Michael

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,048

    Re: Index closest match

    -0.32 and -32% are the same value, so if -32% is giving 0, so will -0.32
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ 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. index/match "closest to X (value)"?
    By ryefield in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-02-2013, 09:25 AM
  2. [SOLVED] Index-Match Closest Value
    By CzechCzar in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-25-2013, 07:40 PM
  3. [SOLVED] Index Match V Look Up Closest Match
    By mhedge in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-04-2013, 03:42 PM
  4. Match the Upper closest value using Index Match.
    By adnanaddo in forum Excel General
    Replies: 3
    Last Post: 01-16-2013, 02:47 AM
  5. Replies: 0
    Last Post: 08-25-2005, 02:37 AM

Tags for this Thread

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