Results 1 to 3 of 3

Thread: Index-Match Nearest Greater Than

  1. #1
    Acolyte Joecam's Avatar
    Join Date
    May 2014
    Posts
    27
    Articles
    0
    Excel Version
    xlsm

    Index-Match Nearest Greater Than



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

    Hi all, I'm looking for some help on a formula that I just can't get working properly.

    I have a data table that looks something like this:
    Column S Column T
    160000 2.499
    165000 2.563
    170000 2.628
    175000 2.692

    In cell 'XI1'!B2 I am storing the value to be looked up - 168000, for example.

    The formula I'm using is: =INDEX('Data Form'!T3:T37,MATCH('XI1'!B2,'Data Form'!S3:S37,1))
    This formula is working as expected, and returning the value of 2.563.

    However, I need to find the next largest number in column T (2.628) so that I can perform work on it.
    When I switch this formula to =INDEX('Data Form'!T3:T37,MATCH('XI1'!B2,'Data Form'!S3:S37,-1))
    I get a #N/A error.
    Stepping into the formula, the error indicates 'XI1'!B2 "The cell currently being evaluated contains a constant."

    Any ideas on what I'm doing wrong? Am I just using the wrong formula for what I want to do?

    Any assistance would be greatly appreciated!

  2. #2
    Wizard Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,615
    Articles
    0
    Excel Version
    2010 on Xubuntu
    With the -1 match type the lookup array must be sorted descending. Perhaps try =INDEX('Data Form'!T3:T37,MATCH('XI1'!B2,'Data Form'!S3:S37,1)+1)

  3. #3
    Acolyte Joecam's Avatar
    Join Date
    May 2014
    Posts
    27
    Articles
    0
    Excel Version
    xlsm
    That worked perfectly, thank you so much for your assistance!

Tags for this Thread

Posting Permissions

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