+ Reply to Thread
Results 1 to 7 of 7

returning greatest and lowest value involving index match formula

  1. #1
    Registered User
    Join Date
    04-09-2014
    Location
    New Zealand
    MS-Off Ver
    Excel 2010
    Posts
    47

    returning greatest and lowest value involving index match formula

    Hi There,


    I am trying to use index match formulas to return the highest and lowest values from an array(My lookup array always includes a negative and positive value for a match type). The formula I am using is below,

    =(INDEX('Results'!R:R,MATCH('Lookup'!B164,'Results'!K:K,0)) where 0 is a 1 to try and return the positive values.

    There are always two values which match my lookup value, a negative value and a positive value.

    I seem to be able to use the above formula to return all the negative values, but when I try and use the following formula =(INDEX('Results'!R:R,MATCH('Lookup'!B164,'Results'!K:K,1)), there are always a few results which are incorrect.

    Does anybody know why this is the case? Or know of a simpler or alternative formula I should be using to return the negative values and positive values?


    Cheers

    Ryan

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,099

    Re: returning greatest and lowest value involving index match formula

    If all you want to do is return the highest and lowest, why not just your MIN/MAX or LARGE/SMALL, instead of index/match?
    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

  3. #3
    Registered User
    Join Date
    04-09-2014
    Location
    New Zealand
    MS-Off Ver
    Excel 2010
    Posts
    47

    Re: returning greatest and lowest value involving index match formula

    Thanks for your reply,

    Sorry my explanation above is not very good. The heading should be 'INDEX MATCH FORMULA TO RETURN TWO VALUES' (one a positive value the other a negative value).

    There are two values which relate to the index match formula. One negative, one positive. The normal index/match formula returns the 1st value it finds relating to the criteria. I need a formula which returns the value that is positive and ignores the negative value, and a formula to do the opposite, return the negative value that correlates to the index/match formula but ignores the positive match.

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,099

    Re: returning greatest and lowest value involving index match formula

    I still dont understand why you cant just use MIN/MAX or LARGE/SMALL instead of INDEX/MATCH. If you need to the 2 values in 1 cell, S/L or I/M would both still need to be combined into 1 cell

  5. #5
    Forum Expert
    Join Date
    11-26-2013
    Location
    Colac, Victoria, Australia
    MS-Off Ver
    Excel 2016
    Posts
    1,308

    Re: returning greatest and lowest value involving index match formula

    Still not clear to me Bridge.

    I think an example would help. Can you post a sample worksheet in which you show the answer you hope to achieve/

    Regards,

    David

  6. #6
    Registered User
    Join Date
    04-09-2014
    Location
    New Zealand
    MS-Off Ver
    Excel 2010
    Posts
    47

    Re: returning greatest and lowest value involving index match formula

    Sample hopefully attached. Hopefully this makes it clear
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    04-09-2014
    Location
    New Zealand
    MS-Off Ver
    Excel 2010
    Posts
    47

    Re: returning greatest and lowest value involving index match formula

    To add to the above, I am wanting formulas that read as such,

    In column A (Positive value), The formula I am wanting reads as such 'Find the corresponding purchase order number in column C within column K of the results tab (there is usually two matches) and return the positive value match from column R.

    Column B (Negative value) reads similar, 'Find the corresponding purchase order number in column C within column K of the results tab and return the negative value match (if it exists) or return "".

    Hope this time it makes sense.

+ 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] INDEX(MATCH(MODE) Formula returning #N/A
    By shellataylor in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-19-2016, 08:40 PM
  2. Sumif involving maybe an OR or index/match formula...
    By bridge4444 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-25-2015, 06:09 AM
  3. [SOLVED] Indirect match index formula question involving different spreadsheet name
    By lilsnoop in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-02-2015, 10:19 PM
  4. [SOLVED] Indirect-index-match formula is returning #Value
    By brent_excel in forum Excel General
    Replies: 3
    Last Post: 08-01-2014, 05:52 PM
  5. Index/Match for a lookup involving 2 criteria
    By OilMan in forum Excel General
    Replies: 8
    Last Post: 10-30-2011, 07:07 PM
  6. Index Max Match formula returning wrong value
    By bk77 in forum Excel General
    Replies: 4
    Last Post: 03-25-2009, 02:17 PM

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