Formula Help

Tennisguuy

Well-known Member
Joined
Oct 17, 2007
Messages
564
Office Version
  1. 2016
Platform
  1. Windows
I have this formula in cell B17

iferror(IF(AND($B$5="",$B$6=""),"",IF(AND($B$6="",$B$7="Yes"),INDEX($AE$6:$AI$54,MATCH($B$5,$AD$6:$AD$54,0),MATCH(A17,$AE$5:$AI$5,0))*$B$10/VLOOKUP($B$5,$AD$6:$AJ$54,7,FALSE),INDEX($AE$70:$AI$75,MATCH($B$6,$AD$70:$AD$75,0),MATCH(A17,$AE$69:$AI$69,0))*$B$10/VLOOKUP($B$6,$AD$70:$AJ$75,7,FALSE))),IF(AND($B$6="",$B$7<>"Yes"),INDEX($AE$6:$AI$54,MATCH($B$5,$AD$6:$AD$54,0),MATCH(A17,$AE$5:$AI$5,0))*$B$10,INDEX($AE$70:$AI$75,MATCH($B$6,$AD$70:$AD$75,0),MATCH(A17,$AE$69:$AI$69,0))*$B$10),0)

I get a you've entered too many arguments for this function. When I remove the iferror and the last parenthesis, last 0 and the common I then then value error. I tried splitting the formula in two parts and when I do that I don't get any errors and it works.

IFERROR(IF(AND($B$6="",$B$7<>"Yes"),INDEX($AE$6:$AI$54,MATCH($B$5,$AD$6:$AD$54,0),MATCH(A17,$AE$5:$AI$5,0))*$B$10,INDEX($AE$70:$AI$75,MATCH($B$6,$AD$70:$AD$75,0),MATCH(A17,$AE$69:$AI$69,0))*$B$10),0). I get value of 135.43 which is correct

IF(AND($B$5="",$B$6=""),"",IF(AND($B$6="",$B$7="Yes"),INDEX($AE$6:$AI$54,MATCH($B$5,$AD$6:$AD$54,0),MATCH(A17,$AE$5:$AI$5,0))*$B$10/VLOOKUP($B$5,$AD$6:$AJ$54,7,FALSE),INDEX($AE$70:$AI$75,MATCH($B$6,$AD$70:$AD$75,0),MATCH(A17,$AE$69:$AI$69,0))*$B$10/VLOOKUP($B$6,$AD$70:$AJ$75,7,FALSE))) I get a value of 67.38

I have values in cell B5, B6 and B7. If I remove the values in B6 and B7 the first part of the formula which is the first one I show split with a value of 135.43 give me a N/A error which I understand because there is no value in B6 or B7 so thought I would but this part last but when I do that I get you've entered too many arguments error again.

Can't figure out what I am doing wrong.
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
You can shorten that formula drastically.
You're using four INDEX()es, dependent on B6 and B7 values.
The first and third INDEX()es are the same, the second and fourth INDEX()es are the same, the only difference is you're adding a VLOOKUP to the formula if B7="Yes"
If you move that B7 condition to just before the VLOOKUP you can either divide by the VLOOKUP value or divide by 1 which, reducing the four INDEX()es to two.

Thus

=IFERROR(IF(AND(B5="",B6=""),"",IF(B6="",INDEX($AE$6:$AI$54,MATCH($B$5,$AD$6:$AD$54,0),MATCH(A17,$AE$5:$AI$5,0))*$B$10/IF(B7="Yes",VLOOKUP($B$5,$AD$6:$AJ$54,7,FALSE),1),INDEX($AE$70:$AI$75,MATCH($B$6,$AD$70:$AD$75,0),MATCH(A17,$AE$69:$AI$69,0))*$B$10/IF(B7="Yes",VLOOKUP($B$6,$AD$70:$AJ$75,7,FALSE),1))),0)
 
Last edited:
Upvote 0
You use three arguments in the IFERROR function, you can only use two.

iferror(IF(AND($B$5="",$B$6=""),"",IF(AND($B$6="",$B$7="Yes"),INDEX($AE$6:$AI$54,MATCH($B$5,$AD$6:$AD$54,0),MATCH(A17,$AE$5:$AI$5,0))*$B$10/VLOOKUP($B$5,$AD$6:$AJ$54,7,FALSE),INDEX($AE$70:$AI$75,MATCH($B$6,$AD$70:$AD$75,0),MATCH(A17,$AE$69:$AI$69,0))*$B$10/VLOOKUP($B$6,$AD$70:$AJ$75,7,FALSE))),IF(AND($B$6="",$B$7<>"Yes"),INDEX($AE$6:$AI$54,MATCH($B$5,$AD$6:$AD$54,0),MATCH(A17,$AE$5:$AI$5,0))*$B$10,INDEX($AE$70:$AI$75,MATCH($B$6,$AD$70:$A D$75,0),MATCH(A17,$AE$69:$AI$69,0))*$B$10),0)

Blue is the first argument.

Red is the second.

Green is the third.
 
Upvote 0
Thanks you for your help I really appreciate it very much. Special-K99 your correction worked perfectly. Thanks you rock. I was racking my brain trying to figure out why it wasn't working.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,210
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top