O

#### oliverbradley

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