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

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.

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

Sample hopefully attached. Hopefully this makes it clear

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.