# returning greatest and lowest value involving index match formula

1. ## 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. ## 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?

3. ## Re: returning greatest and lowest value involving index match formula

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. ## 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. ## 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. ## Re: returning greatest and lowest value involving index match formula

Sample hopefully attached. Hopefully this makes it clear

7. ## 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.

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

#### 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