IFERROR backwards compatible

[CMMilne]

Hello - I am trying to make the following IFERROR backwards compatible and I am running into problems. I tried changing it to IF(ISERROR... but I keep getting "too many arguments" and I try to adjust and I keep getting into a circular loop. Any suggestions:

(IFERROR(VLOOKUP(CONCATENATE('Input Sheet'!$C$8,'InputSheet'!$C$10,IFERROR(MIN(12,DATEDIF('InputSheet'!$C$6,DATE(B3,1,1),"Y")+1),0)),INDIRECT('InputSheet'!$C$8&"pay"),HLOOKUP('Input Sheet'!$C$9,INDIRECT('InputSheet'!$C$8&"pay"),2,FALSE),FALSE),0)*'InputSheet'!$C$11*12*(DATE(B3,MONTH('Input Sheet'!$C$6),DAY('InputSheet'!$C$6))-DATE(B3,1,1))/365.25)+(VLOOKUP(CONCATENATE('InputSheet'!$C$8,'Input Sheet'!$C$10,MIN(12,DATEDIF('InputSheet'!$C$6,DATE(B3,12,31),"Y")+1)),INDIRECT('Input Sheet'!$C$8&"pay"),HLOOKUP('InputSheet'!$C$9,INDIRECT('InputSheet'!$C$8&"pay"),2,FALSE),FALSE)*'InputSheet'!$C$11*12*(1-(DATE(B3,MONTH('Input Sheet'!$C$6),DAY('InputSheet'!$C$6))-DATE(B3,1,1))/365.25))

 

[StephenCrump]

Welcome to the Forum!

If your formula is working correctly, then this should do it:

=(IF(ISERROR(VLOOKUP(CONCATENATE('Input Sheet'!$C$8,InputSheet!$C$10,IFERROR(MIN(12,DATEDIF(InputSheet!$C$6,DATE(B3,1,1),"Y")+1),0)),INDIRECT(InputSheet!$C$8&"pay"),HLOOKUP('Input Sheet'!$C$9,INDIRECT(InputSheet!$C$8&"pay"),2,FALSE),FALSE)),0,VLOOKUP(CONCATENATE('Input Sheet'!$C$8,InputSheet!$C$10,IFERROR(MIN(12,DATEDIF(InputSheet!$C$6,DATE(B3,1,1),"Y")+1),0)),INDIRECT(InputSheet!$C$8&"pay"),HLOOKUP('Input Sheet'!$C$9,INDIRECT(InputSheet!$C$8&"pay"),2,FALSE),FALSE))*InputSheet!$C$11*12*(DATE(B3,MONTH('Input Sheet'!$C$6),DAY(InputSheet!$C$6))-DATE(B3,1,1))/365.25)+(VLOOKUP(CONCATENATE(InputSheet!$C$8,'Input Sheet'!$C$10,MIN(12,DATEDIF(InputSheet!$C$6,DATE(B3,12,31),"Y")+1)),INDIRECT('Input Sheet'!$C$8&"pay"),HLOOKUP(InputSheet!$C$9,INDIRECT(InputSheet!$C$8&"pay"),2,FALSE),FALSE)*InputSheet!$C$11*12*(1-(DATE(B3,MONTH('Input Sheet'!$C$6),DAY(InputSheet!$C$6))-DATE(B3,1,1))/365.25))

But two observations:

1. There will almost certainly be a smarter way to do this.

2. Is your original formula correct? I would have guessed that if the first Vlookup errors, then the second Vlookup will also error, but the formula doesn't test for this, and always uses the second Vlookup value.

 

[CMMilne]

Thank you! I am sure there is a smarter way to do this but I couldn't get the formula to work with certain parameters based on hire dates, pay increases and IRS regs. Would I need to change the other 2 IFERROR to IF(ISERROR)?

 

[StephenCrump]

Thank you! I am sure there is a smarter way to do this but I couldn't get the formula to work with certain parameters based on hire dates, pay increases and IRS regs. Would I need to change the other 2 IFERROR to IF(ISERROR)?

Sorry, I missed the other IFERROR(MIN(12,DATEDIF(C6,DATE(B3,1,1),"Y")+1),0)

There will be a simpler way to do this bit, but I'm not clear what you're trying to do? On the face of it, it looks like you're calculating years and part years, but then the formula won't work correctly if C6 is a 1 January date. And the parameter of 12 suggests that you really intend measuring months, rather than years?

I can't make head or tail of the rest of your formula without knowing the layout of your workbook, and what you're trying to do. I'd also be surprised if the IRS was calculating year fractions based on 365.25 days in the year?

If you'd like to post more details about what you're trying to do, i.e. an example with the solution you're expecting, I'm sure someone here can simplify your formula.

Two options:

1. Post screenshots, using one of the methods outlined in Part B here: https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html

2. Post your workbook (provided not confidential/sensitive data) to a third party site such as box.com and post the link here.

The first is the better option, as not everyone can/wants to download files from other sites.

 

[CMMilne]

Hello - I figured it out. Yes, it is a retirement calculator based upon years and part years of being in one pay grade vs. another or changing equipment (which leads to the change in pay), this in turn effects the amount of money the person can earn, contribute and have a match. The reason we are using 365.25 days in a year because specifically that is how many days there are in a year based on other calculations. It keeps them consistent.

=IF(ISERROR(VLOOKUP(CONCATENATE('Input Sheet'!$C$8,'Input Sheet'!$C$10,MIN(12,DATEDIF('Input Sheet'!$C$6,DATE(B3,1,1),"Y")+1)),INDIRECT('Input Sheet'!$C$8&"pay"),HLOOKUP('Input Sheet'!$C$9,INDIRECT('Input Sheet'!$C$8&"pay"),2,FALSE),FALSE)),0,(VLOOKUP(CONCATENATE('Input Sheet'!$C$8,'Input Sheet'!$C$10,MIN(12,DATEDIF('Input Sheet'!$C$6,DATE(B3,1,1),"Y")+1)),INDIRECT('Input Sheet'!$C$8&"pay"),HLOOKUP('Input Sheet'!$C$9,INDIRECT('Input Sheet'!$C$8&"pay"),2,FALSE),FALSE))*'Input Sheet'!$C$11*12*(DATE(B3,MONTH('Input Sheet'!$C$6),DAY('Input Sheet'!$C$6))-DATE(B3,1,1))/365.25)+(VLOOKUP(CONCATENATE('Input Sheet'!$C$8,'Input Sheet'!$C$10,MIN(12,DATEDIF('Input Sheet'!$C$6,DATE(B3,12,31),"Y")+1)),INDIRECT('Input Sheet'!$C$8&"pay"),HLOOKUP('Input Sheet'!$C$9,INDIRECT('Input Sheet'!$C$8&"pay"),2,FALSE),FALSE)*'Input Sheet'!$C$11*12*(1-(DATE(B3,MONTH('Input Sheet'!$C$6),DAY('Input Sheet'!$C$6))-DATE(B3,1,1))/365.25))