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]

channel	sku id	item title	vat	qty	city	zone	weight	total weight	actual total shipping	shipping cost
flipkart	NK-620-SCRNPRTCTR <tbody> </tbody>	Screen Guard Nokia Lumia 620 Protector Scratch <tbody> </tbody>	0.145 <tbody> </tbody>	1 <tbody> </tbody>	Coimbatore <tbody> </tbody>	national <tbody> </tbody>	50 <tbody> </tbody>	50 <tbody> </tbody>	500 <tbody> </tbody>	55 <tbody> </tbody>

<tbody>
</tbody>

shippingflipkart

local	20	40	60	80
national	70	100	130	160

<tbody>
</tbody>

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.