Morning all,

=INDEX('[PPfV F A.xls]input plus'!$D$1755:$D$1798,MATCH(1,('[PPfV F
A.xls]input plus'!$DJ$1755:$DJ$1798=E5)*('[PPfV F A.xls]input
plus'!$DG$1755:$DG$1798=MIN('[PPfV F A.xls]input
plus'!$DG$1755:$DG$1798)),0))

What im trying to achieve is for the formula to always pick the lowest
and next lowest value from the range DG1755:DG1798 based on E5 always
being a set value.

example: E5 specifies a risk value (say 2). DG1755:DG1798 contains
products with a product rating.

ie the no1 product has a risk val of 2
..........no2 product has a risk val of 2
..........no3 product has a risk val of 4
..........no4 product has a risk val of 2

What I want is for that formula to report back product 1, 2 and 4
IGNORING product 3 because its risk val is different.

Im nearly there, but not quite, any help is greatly appreciated!


--
chrisrowe_cr
------------------------------------------------------------------------
chrisrowe_cr's Profile: http://www.excelforum.com/member.php...o&userid=25220
View this thread: http://www.excelforum.com/showthread...hreadid=388983

Hi!

ie the no1 product has a risk val of 2
..........no2 product has a risk val of 2
..........no3 product has a risk val of 4
..........no4 product has a risk val of 2

What I want is for that formula to report back product 1, 2 and 4
IGNORING product 3 because its risk val is different.

The formula can only return a single value. Based on your explanation above,
you'd need 3 formulas but then it would get really complicated judging from
the possible combinations of risk values.

What if all of the above had a rv of 2, would you want all 4 products
returned? If so, then you'd need 4 formulas.

Biff

"chrisrowe_cr"
wrote in message
news:chrisrowe_cr.1siibt_1121936825.7048@excelforu m-nospam.com...

Morning all,

=INDEX('[PPfV F A.xls]input plus'!$D$1755:$D$1798,MATCH(1,('[PPfV F
A.xls]input plus'!$DJ$1755:$DJ$1798=E5)*('[PPfV F A.xls]input
plus'!$DG$1755:$DG$1798=MIN('[PPfV F A.xls]input
plus'!$DG$1755:$DG$1798)),0))

What im trying to achieve is for the formula to always pick the lowest
and next lowest value from the range DG1755:DG1798 based on E5 always
being a set value.

example: E5 specifies a risk value (say 2). DG1755:DG1798 contains
products with a product rating.

ie the no1 product has a risk val of 2
.........no2 product has a risk val of 2
.........no3 product has a risk val of 4
.........no4 product has a risk val of 2

What I want is for that formula to report back product 1, 2 and 4
IGNORING product 3 because its risk val is different.

Im nearly there, but not quite, any help is greatly appreciated!


--
chrisrowe_cr
------------------------------------------------------------------------
chrisrowe_cr's Profile:
http://www.excelforum.com/member.php...o&userid=25220
View this thread: http://www.excelforum.com/showthread...hreadid=388983