Insert 2 INDEX/MATCH functions into an IF function
Ecam39
Posted messages
314
Status
Member
-
Ecam39 Posted messages 314 Status Member -
Ecam39 Posted messages 314 Status Member -
Hello,
I need to look up a transport cost in a pricing grid based on two criteria (quantity + Department). For this, I combined the INDEX + MATCH functions, which gives this:
=INDEX(Price Grid;MATCH(Lookup Value;department list;0);MATCH(Lookup Value;palette quantity list;0))
I just need to enter the number of palettes and the delivery department, and I have my price.
But I would like to insert a second pricing grid and have Excel say that if, for example, the quantity is less than 10, then it will look for the price in grid A (INDEX + MATCH grid A), if it is above (thus false value), it will take from grid B (INDEX + MATCH grid B)
This results in 2 INDEX + MATCH functions, but I do not know how to write it. I tried to put brackets to clearly separate the TRUE and FALSE values, but it does not recognize them.
For now, I wrote it like this:
=IF(B5<10;INDEX(Price Grid A;MATCH(Lookup Value;department list;0);MATCH(Lookup Value;palette quantity list;0);INDEX(Price Grid B;MATCH(Lookup Value;department list;0);MATCH(Lookup Value;palette quantity list;0))
What is in bold corresponds to the TRUE part, and the rest to the FALSE part
Configuration: Windows 7 / Internet Explorer 9.0
I need to look up a transport cost in a pricing grid based on two criteria (quantity + Department). For this, I combined the INDEX + MATCH functions, which gives this:
=INDEX(Price Grid;MATCH(Lookup Value;department list;0);MATCH(Lookup Value;palette quantity list;0))
I just need to enter the number of palettes and the delivery department, and I have my price.
But I would like to insert a second pricing grid and have Excel say that if, for example, the quantity is less than 10, then it will look for the price in grid A (INDEX + MATCH grid A), if it is above (thus false value), it will take from grid B (INDEX + MATCH grid B)
This results in 2 INDEX + MATCH functions, but I do not know how to write it. I tried to put brackets to clearly separate the TRUE and FALSE values, but it does not recognize them.
For now, I wrote it like this:
=IF(B5<10;INDEX(Price Grid A;MATCH(Lookup Value;department list;0);MATCH(Lookup Value;palette quantity list;0);INDEX(Price Grid B;MATCH(Lookup Value;department list;0);MATCH(Lookup Value;palette quantity list;0))
What is in bold corresponds to the TRUE part, and the rest to the FALSE part
Configuration: Windows 7 / Internet Explorer 9.0
7 answers
Hello,
your formula is missing parentheses. If you count correctly, you have 7 opening parentheses but only 5 closing parentheses.
Try this:
The bolded parentheses are the ones I added, and they correspond to the opening parentheses of the INDEX functions that you hadn't closed.
Best regards.
--
This guy wasn't very bright, because he was foolish.
your formula is missing parentheses. If you count correctly, you have 7 opening parentheses but only 5 closing parentheses.
Try this:
=IF(B5<10;INDEX(Price Grid A;MATCH(Looked-up Value;department list;0);MATCH(Looked-up Value;palette quantity list;0));INDEX(Price Grid B;MATCH(Looked-up Value;department list;0);MATCH(Looked-up Value;palette quantity list;0)))
The bolded parentheses are the ones I added, and they correspond to the opening parentheses of the INDEX functions that you hadn't closed.
Best regards.
--
This guy wasn't very bright, because he was foolish.
Hello,
There is another form of the Index function: INDEX(ref; row_num; column_num; area_num)
Which would give =INDEX((GridA; GridB); MATCH(Value sought; department list; 0); MATCH(Value sought; palette quantity list; 0); IF(B5>10; 1; 2)
Where GridA is a field of values (without headers)
Other procedures are possible. You can define the field with an Offset function or with Indirect and named fields.
There is another form of the Index function: INDEX(ref; row_num; column_num; area_num)
Which would give =INDEX((GridA; GridB); MATCH(Value sought; department list; 0); MATCH(Value sought; palette quantity list; 0); IF(B5>10; 1; 2)
Where GridA is a field of values (without headers)
Other procedures are possible. You can define the field with an Offset function or with Indirect and named fields.
It’s indeed a parentheses issue. Where I had put brackets, I needed to put parentheses.
Thank you