INDEX and MATCH with range
 

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

INDEX and MATCH with range
 

Try

=INDEX('Price data'!$I$2:$I$29,
MATCH(1,('Price data'!$A$2:$A29=InputProduct)*
('Price data'!$F$2:$F$29<InputNumber)*
('Price data'!$G$2:$G$29InputNumber)*
('Price
data'!$H$2:$H$29=InputCurrency),1))

--

HTH

Bob

"oliverbradley" wrote in message
...
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

INDEX and MATCH with range
 

On Mar 29, 9:44*pm, "Bob Phillips" wrote:
Try

=INDEX('Price data'!$I$2:$I$29,
* * * * * * * * MATCH(1,('Price data'!$A$2:$A29=InputProduct)*
* * * * * * * * * * * * * * * * * ('Price data'!$F$2:$F$29<InputNumber)*
* * * * * * * * * * * * * * * * * ('Price data'!$G$2:$G$29InputNumber)*
* * * * * * * * * * * * * * * * * ('Price data'!$H$2:$H$29=InputCurrency),1))

--

HTH

Bob

Duh! Of course the Match is an AND already. Thank you so much - I
would have stared at that for days.

INDEX and MATCH with range
 

No, it is not the MATCH that is an AND, it is the * that acts like AND, and
your original formula already had one.

--

HTH

Bob

"oliver.bradley" wrote in message
...
On Mar 29, 9:44 pm, "Bob Phillips" wrote:
Try

=INDEX('Price data'!$I$2:$I$29,
MATCH(1,('Price data'!$A$2:$A29=InputProduct)*
('Price data'!$F$2:$F$29<InputNumber)*
('Price data'!$G$2:$G$29InputNumber)*
('Price data'!$H$2:$H$29=InputCurrency),1))

--

HTH

Bob

Duh! Of course the Match is an AND already. Thank you so much - I
would have stared at that for days.