INDEX and MATCH with range

[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

 

[Bob Phillips]

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$29>InputNumber)*
('Price
data'!$H$2:$H$29=InputCurrency),1))

 

[oliver.bradley]

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

 

[Bob Phillips]

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

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