Tennisguuy
Well-known Member
- Joined
- Oct 17, 2007
- Messages
- 564
- Office Version
- 2016
- Platform
- 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.
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.