Max Number of Arguments within an Excel Function

jallenamdg

New Member
Joined
Oct 21, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
OK....I'm trying to add an additional block to find the value on another sheet (with the named references). I've formatted it for easier comparison but in actuality this formula is set within a VBA module with a simple assignment statement.

Here's the Original - Obviously, it works fine as is and works if I substitute 4 for 3 on the "..CCR_X" reference (Ex. WO_KeyCCR_4)
=IF(
RC4="","",
IF(NOT(ISERROR(MATCH(RC4,WO_KeyCCR_1,0))),INDEX(WO_TblCCR_1,MATCH(RC4,WO_KeyCCR_1,0),MATCH("WO Qty",WO_HdrCCR_1,0)),
IF(NOT(ISERROR(MATCH(RC4,WO_KeyCCR_2,0))),INDEX(WO_TblCCR_2,MATCH(RC4,WO_KeyCCR_2,0),MATCH("WO Qty",WO_HdrCCR_2,0)),
IF(NOT(ISERROR(MATCH(RC4,WO_KeyCCR_3,0))),INDEX(WO_TblCCR_3,MATCH(RC4,WO_KeyCCR_3,0),MATCH("WO Qty",WO_HdrCCR_3,0)),
"Not in Source Data"))))


New - Application Error during assignment statement (R/T Error 1004 - Application-defined or object-defined error)
=IF(
RC4="","",
IF(NOT(ISERROR(MATCH(RC4,WO_KeyCCR_1,0))),INDEX(WO_TblCCR_1,MATCH(RC4,WO_KeyCCR_1,0),MATCH("WO Qty",WO_HdrCCR_1,0)),
IF(NOT(ISERROR(MATCH(RC4,WO_KeyCCR_2,0))),INDEX(WO_TblCCR_2,MATCH(RC4,WO_KeyCCR_2,0),MATCH("WO Qty",WO_HdrCCR_2,0)),
IF(NOT(ISERROR(MATCH(RC4,WO_KeyCCR_3,0))),INDEX(WO_TblCCR_3,MATCH(RC4,WO_KeyCCR_3,0),MATCH("WO Qty",WO_HdrCCR_3,0)),
IF(NOT(ISERROR(MATCH(RC4,WO_KeyCCR_4,0))),INDEX(WO_TblCCR_4,MATCH(RC4,WO_KeyCCR_4,0),MATCH("WO Qty",WO_HdrCCR_4,0)),
"Not in Source Data")))))

While the number of IF blocks in the new one doesn't exceed 7, I'm wondering if the additional block is exceeding some other limit on complexity?

Thanks!
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

jallenamdg

New Member
Joined
Oct 21, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
FYI - I've already experimented with the number of parentheses at the end to no avail....
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,255
Office Version
  1. 365
Platform
  1. Windows
If you are getting an RTE then we need to see the code.
 

jallenamdg

New Member
Joined
Oct 21, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
VBA Code:
Range(Cells(2, coltrgt1).Address & ":" & Cells(RowLast, coltrgt1).Address).FormulaR1C1 = _
        "=IF(RC" & coltrgt2 & "="""","""",IF(NOT(ISERROR(MATCH(RC" & coltrgt2 & ",WO_KeyCCR_1,0))),INDEX(WO_TblCCR_1,MATCH(RC" & coltrgt2 & ",WO_KeyCCR_1,0),MATCH(""WO Qty"",WO_HdrCCR_1,0)),IF(NOT(ISERROR(MATCH(RC" & coltrgt2 & ",WO_KeyCCR_2,0))),INDEX(WO_TblCCR_2,MATCH(RC" & coltrgt2 & ",WO_KeyCCR_2,0),MATCH(""WO Qty"",WO_HdrCCR_2,0)),IF(NOT(ISERROR(MATCH(RC" & coltrgt2 & ",WO_KeyCCR_3,0))),INDEX(WO_TblCCR_3,MATCH(RC" & coltrgt2 & ",WO_KeyCCR_3,0),MATCH(""WO Qty"",WO_HdrCCR_3,0))),IF(NOT(ISERROR(MATCH(RC" & coltrgt2 & ",WO_KeyCCR_4,0))),INDEX(WO_TblCCR_4,MATCH(RC" & coltrgt2 & ",WO_KeyCCR_4,0),MATCH(""WO Qty"",WO_HdrCCR_4,0)),""Not in Source Data"")))))"
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,255
Office Version
  1. 365
Platform
  1. Windows
Thanks for that, you've got an extra bracket in there, try
VBA Code:
Range(Cells(2, coltrgt1), Cells(RowLast, coltrgt1)).FormulaR1C1 = _
        "=IF(RC" & coltrgt2 & "="""","""",IF(NOT(ISERROR(MATCH(RC" & coltrgt2 & ",WO_KeyCCR_1,0))),INDEX(WO_TblCCR_1,MATCH(RC" & coltrgt2 & ",WO_KeyCCR_1,0),MATCH(""WO Qty"",WO_HdrCCR_1,0))" & _
        ",IF(NOT(ISERROR(MATCH(RC" & coltrgt2 & ",WO_KeyCCR_2,0))),INDEX(WO_TblCCR_2,MATCH(RC" & coltrgt2 & ",WO_KeyCCR_2,0),MATCH(""WO Qty"",WO_HdrCCR_2,0))" & _
        ",IF(NOT(ISERROR(MATCH(RC" & coltrgt2 & ",WO_KeyCCR_3,0))),INDEX(WO_TblCCR_3,MATCH(RC" & coltrgt2 & ",WO_KeyCCR_3,0),MATCH(""WO Qty"",WO_HdrCCR_3,0))" & _
        ",IF(NOT(ISERROR(MATCH(RC" & coltrgt2 & ",WO_KeyCCR_4,0))),INDEX(WO_TblCCR_4,MATCH(RC" & coltrgt2 & ",WO_KeyCCR_4,0),MATCH(""WO Qty"",WO_HdrCCR_4,0)),""Not in Source Data"")))))"
 

Watch MrExcel Video

Forum statistics

Threads
1,123,223
Messages
5,600,399
Members
414,383
Latest member
kevinlarey

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
Top