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$29InputNumber))*('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