# INDEX and MATCH with range

O

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?

Oliver

B

#### 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))

O

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.

B

#### Bob Phillips

No, it is not the MATCH that is an AND, it is the * that acts like AND, and

--

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.