+ Reply to Thread
Results 1 to 10 of 10

INDEX & MATCH to match nearest value?

  1. #1
    Forum Contributor
    Join Date
    01-11-2007
    Posts
    127

    INDEX & MATCH to match nearest value?

    =IF(AND(C12=1,C13=1),INDEX(S9:S109,MATCH(C21,M9:M109,0)),0)

    All cells and ranges in the formula are formatted as 'Accounting'. At present, the majority of the time an exact match will be found with C21 in M9:M109. Occasionally, an exact match isn't found, and on this occasion I'd like the closest value in M9:M109 to be matched and the corresponding value in S9:S109 to be returned. Can anyone help? Thanks.

  2. #2
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: INDEX & MATCH to match nearest value?

    If i understand well, then..


    =IF(AND(C12=1,C13=1),INDEX(S9:S109,MATCH(C21,M9:M109,0)),1)
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  3. #3
    Forum Expert GeneralDisarray's Avatar
    Join Date
    09-15-2011
    Location
    Pittsburgh, PA, USA
    MS-Off Ver
    Windows Excel 2016
    Posts
    1,416

    Re: INDEX & MATCH to match nearest value?

    doesn't the 1 need to be in the Match function?

    =IF(AND(C12=1,C13=1),INDEX(S9:S109,MATCH(C21,M9:M109,1)),0)

    THe 1 tells match to return "the largest value that is less than or equal to the lookup value"

    EDIT:
    Also, using "1" for the match type parameter requires the column to be sorted in Ascending order.

    Using "-1" for match type returns the "smallest value that is greater than or equal to the lookup value" and requires the match column to be sorted in Descending order
    Last edited by GeneralDisarray; 06-29-2012 at 10:20 AM.
    Remember, saying thanks only takes a second or two. Click the star icon(*) below the post you liked, to give some Rep if you think an answer deserves it.

    Please,mark your thread [SOLVED] if you received your answer.

  4. #4
    Forum Contributor
    Join Date
    01-11-2007
    Posts
    127

    Re: INDEX & MATCH to match nearest value?

    The formula still seems to return #N/A when a match is not found. Your formula is the same as the original apart from a '1' at the end instead of a '0'. What does the '1' do?

  5. #5
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Northeast USA
    MS-Off Ver
    Office 365
    Posts
    2,043

    Re: INDEX & MATCH to match nearest value?

    Let's talk about the syntax of the MATCH function.

    Three parts: MATCH(lookup_value, lookup_array, match_type)
    The first bit is the value you are passing to the MATCH to find search for,
    And you're searching for its position inside the array you pass as the second term.

    What you are asking about is, the third term, match_type.
    It can have three values: 1 (assumed if omitted), 0, or -1
    What they mean:

    1: Return largest value less than or equal to the lookup_value. The array must be in ascending order--if it's unsorted, you'll get the first local maxima, the first term preceding the first value that's too big, not the "ideal" match.

    0: Return only an exact match to the lookup_value. But, order doesn't matter.

    -1: Return the smallest value greater than or equal to the lookup_value. The array must be in descending order, for the same reasons as we saw for "1".

    ANYWAY, what you want to do is change that 0 in the last term in MATCH() to either 1 to -1, depending if you want the "next smallest" or "next largest" value to the exact match. BUT in that case your array needs to be sorted appropriately--which can be done with Sort & Filter.

  6. #6
    Forum Expert GeneralDisarray's Avatar
    Join Date
    09-15-2011
    Location
    Pittsburgh, PA, USA
    MS-Off Ver
    Windows Excel 2016
    Posts
    1,416

    Re: INDEX & MATCH to match nearest value?

    dang is there an echo in here now you know...and knowing is half the battle!

  7. #7
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: INDEX & MATCH to match nearest value?

    So, after so "large" analysis of what MATCH function, does, if noone of the suggestions gives you the correct result, maybe you must upload a sample workbook.

  8. #8
    Forum Contributor
    Join Date
    01-11-2007
    Posts
    127

    Re: INDEX & MATCH to match nearest value?

    Ok, thanks. Read up on the syntax for MATCH. However, my table can't be sorted in ascending or descending order as it is a simulation of events. I've used the =RAND() in one column to generate numbers in another. I don't want to re-order this as it will upset results. What can be done in this situation? Thanks.

  9. #9
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: INDEX & MATCH to match nearest value?

    Try:

    =IF(AND(C12=1,C13=1),INDEX(S9:S109,MATCH(MIN(INDEX(ABS(M9:M109-C21),0)),INDEX(ABS(M9:M109-C21),0),0)),0)
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  10. #10
    Forum Contributor
    Join Date
    01-11-2007
    Posts
    127

    Re: INDEX & MATCH to match nearest value?

    Thanks NBVC, that's solved the problem. Thanks to everyone else for their input also. Dan

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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