O
oliverbradley
Hi,
I'm trying to build a formula which looks at a set of data and
compares them against 3 variables:
product (text)
currency (text)
number (number)
to get a price per unit.
the data has a minimum (Price data!F2:29) and maximum (Price data!
G2:29) for the number of units (e.g. price x is valid for numbers
between 1-250 units, price y for 250-500 units). The different
products have different ranges that the bands per unit are valid for.
I've got
{=INDEX('Price data'!$I$2:$I$29,MATCH(1,('Price data'!$A$2:$A
$29=InputProduct)*('Price data'!$G$2:$G$29=InputNumber)*('Price data'!
$H$2:$H$29=InputCurrency),1))}
to work, but it doesn't handle the ranges (i.e. InputNumber has to
match the max). I've tried:
{=INDEX('Price data'!$I$2:$I$29,MATCH(1,('Price data'!$A$2:$A
$29=InputProduct)*AND(('Price data'!$F$2:$F$29<InputNumber),('Price
data'!$G$2:$G$29>InputNumber))*('Price data'!$H$2:$H$29=InputCurrency),
1))}
but this returns N/A - I assumed that anything that returns a logical
result (the AND clause) would work in the array.
Can anyone help with my logic? Or suggest alternative approaches?
Input gratefully received,
Oliver
I'm trying to build a formula which looks at a set of data and
compares them against 3 variables:
product (text)
currency (text)
number (number)
to get a price per unit.
the data has a minimum (Price data!F2:29) and maximum (Price data!
G2:29) for the number of units (e.g. price x is valid for numbers
between 1-250 units, price y for 250-500 units). The different
products have different ranges that the bands per unit are valid for.
I've got
{=INDEX('Price data'!$I$2:$I$29,MATCH(1,('Price data'!$A$2:$A
$29=InputProduct)*('Price data'!$G$2:$G$29=InputNumber)*('Price data'!
$H$2:$H$29=InputCurrency),1))}
to work, but it doesn't handle the ranges (i.e. InputNumber has to
match the max). I've tried:
{=INDEX('Price data'!$I$2:$I$29,MATCH(1,('Price data'!$A$2:$A
$29=InputProduct)*AND(('Price data'!$F$2:$F$29<InputNumber),('Price
data'!$G$2:$G$29>InputNumber))*('Price data'!$H$2:$H$29=InputCurrency),
1))}
but this returns N/A - I assumed that anything that returns a logical
result (the AND clause) would work in the array.
Can anyone help with my logic? Or suggest alternative approaches?
Input gratefully received,
Oliver