You have entered too many arguments for this function

[hershpatel]

Hi I am trying to check if the cell [@channel] condition is met before doing a index and match function


=IF([@channel]="flipkart",INDEX(shippingflipkart!$B$2:$Q$3,MATCH('day1'!I4,shippingflipkart!$A$2:$A$3),MATCH(L4,shippingflipkart!$B$1:$Q$1)),IF([@channel]="amazon",INDEX(shippingamazon!$B$2:$Q$3,MATCH('day1'!I4,shippingamazon!$A$2:$A$3),MATCH(L4,shippingamazon!$B$1:$Q$1))), (if([@channel]="rediff", INDEX(shippingrediff!$B$2:$Q$3,MATCH('day1'!I4,shippingrediff!$A$2:$A$3),MATCH(L4,shippingrediff!$B$1:$Q$1)))))

The fomula works fine but as soon as i add the last if statement it gives me an error. You have entered too many arguments for this function

I actually need to add 3 more if statements after this. Please help

 

[Comfy]

Why do you need the IF statements??

[@channel]="flipkart",INDEX(shippingflipkart!$B$2:$Q$3,MATCH('day1'!I4,shippingflipkart!$A$2:$A$3),MATCH(L4,shippingflipkart!$B$1:$Q$1))
[@channel]="amazon",INDEX(shippingamazon!$B$2:$Q$3,MATCH('day1'!I4,shippingamazon!$A$2:$A$3),MATCH(L4,shippingamazon!$B$1:$Q$1))
[@channel]="rediff", INDEX(shippingrediff!$B$2:$Q$3,MATCH('day1'!I4,shippingrediff!$A$2:$A$3),MATCH(L4,shippingrediff!$B$1:$Q$1))

Your INDEX formulas are exactly the same.

 

[hershpatel]

Thank you for the quick reply

I first got to check if [@channel] = "flipkart" and if it is equal then I am using the index and match function to chek for two conditions in the current sheet and then pull the values from another sheet.

I hope that made sense

 

[FormR]

Here is an un-tested (and volatile) option you could try.

=INDEX(INDIRECT("shipping"&[@channel]&"!$B$2:$Q$3"),MATCH('day1'!I4,INDIRECT("shipping" & [@channel]&"!$A$2:$A$3")),MATCH(L4,INDIRECT("shipping" & [@channel]&"!$B$1:$Q$1")))

 

[hershpatel]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]channel[/TD]
[TD]sku id[/TD]
[TD]item title[/TD]
[TD]vat[/TD]
[TD]qty[/TD]
[TD]city[/TD]
[TD]zone[/TD]
[TD]weight[/TD]
[TD]total weight[/TD]
[TD]actual total shipping[/TD]
[TD]shipping cost[/TD]
[/TR]
[TR]
[TD]flipkart[/TD]
[TD][TABLE="width: 166"]
<tbody>[TR]
[TD="width: 166"]NK-620-SCRNPRTCTR[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 95"]
<tbody>[TR]
[TD="width: 95"]Screen Guard Nokia Lumia 620 Protector Scratch[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 104"]
<tbody>[TR]
[TD="class: xl65, width: 104, align: right"]0.145[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Coimbatore[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 73"]
<tbody>[TR]
[TD="width: 73"]national[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 115"]
<tbody>[TR]
[TD="width: 115, align: right"]50[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 167"]
<tbody>[TR]
[TD="width: 167, align: right"]50[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 117"]
<tbody>[TR]
[TD="width: 117, align: right"]500[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 96"]
<tbody>[TR]
[TD="width: 96, align: right"]55[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


shippingflipkart

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]local[/TD]
[TD]20[/TD]
[TD]40[/TD]
[TD]60[/TD]
[TD]80[/TD]
[/TR]
[TR]
[TD]national[/TD]
[TD]70[/TD]
[TD]100[/TD]
[TD]130[/TD]
[TD]160[/TD]
[/TR]
</tbody>[/TABLE]

Now first I check if IF([@channel]="flipkart" if it does then check the values of national and weight from shippingflipkart sheet

same process if IF([@channel]="amazon"
IF([@channel]="rediff"

As all these companies have different shipping rates
I hope this makes sense

 

[Comfy]

Thank you for the quick reply

I first got to check if [@channel] = "flipkart" and if it is equal then I am using the index and match function to chek for two conditions in the current sheet and then pull the values from another sheet.

I hope that made sense

Ah, I see the difference now. Oops.

FormR has an INDIRECT solution.

 

[hershpatel]

Thank you for this

I am sorry i am at new at excel. I pasted in the formula and it seems to work but i have no clue how it works. What if i need to add more conditions i need to add three more

 

[hershpatel]

Hi Thank you for this formula it works well, you are a life saver. I will learn more about using indirect function

 

[FormR]

Glad it works - It is using the @Channel field in the table to dynamically choose the appropriate sheet to perform the lookup's in.

A word of warning - INDIRECT() is a volatile function, this means it recalculates every time the workbook does regardless of whether any precedent cells have changed. For this reason it should be used fairly sparingly.