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:=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. -
-
-
I managed to find out that where I wanted to put brackets, I should have used parentheses; it's that simple^^
I have trouble understanding how a VLOOKUP can replace a combination of INDEX+MATCH and on top of that, on 2 different grids. -
I see how the formula works, but how come for the line of the number of pallets, we don't use a VLOOKUP instead of a MATCH?
-
