ISNA formula help

[AmandaNoyes]

Hi all - so I know how to use the ISNA formula with a Vlookup...BUT...I've tried and tried and can't figure out how to use it if you are using nested IF and VLookup.

Here is my current formula:
=IF($E:$E=15%,VLOOKUP($D:$D,rates,8,FALSE),IF($E:$E=30%,VLOOKUP($D:$D,rates,12,FALSE),IF($E:$E="Default",VLOOKUP($D:$D,rates,4,FALSE),"")))

What I need is for it to return the N/A results as 0. How do I do that? Or is there a better way other than ISNA?

 

[lrobbo314]

With ISNA

=IF(ISNA(IF($E:$E=15%,VLOOKUP($D:$D,rates,8,FALSE),IF($E:$E=30%,VLOOKUP($D:$D,rates,12,FALSE),IF($E:$E="Default",VLOOKUP($D:$D,rates,4,FALSE),"")))),0,IF($E:$E=15%,VLOOKUP($D:$D,rates,8,FALSE),IF($E:$E=30%,VLOOKUP($D:$D,rates,12,FALSE),IF($E:$E="Default",VLOOKUP($D:$D,rates,4,FALSE),""))))

If you have Excel 2010 or newer I believe then you can use IFERROR

=IFERROR(IF($E:$E=15%,VLOOKUP($D:$D,rates,8,FALSE),IF($E:$E=30%,VLOOKUP($D:$D,rates,12,FALSE),IF($E:$E="Default",VLOOKUP($D:$D,rates,4,FALSE),""))),0)

 

[Jonmo1]

Welcome to the board.

Here's a simplified version of the original formula.
Instead of having 3 vlookups (essentially all the same except the column index #)
Do the IF inside a single vlookup in the column index argument.

=VLOOKUP($D2,rates,IF($E2=15%,8,IF($E2=30%,12,IF($E2="Default",4))),FALSE)

To handle the #N/A, depending on your version of XL
XL2007+
=IFERROR(VLOOKUP($D2,rates,IF($E2=15%,8,IF($E2=30%,12,IF($E2="Default",4))),FALSE),0)

XL2003
=IF(ISNA(VLOOKUP($D2,rates,IF($E2=15%,8,IF($E2=30%,12,IF($E2="Default",4))),FALSE)),0,VLOOKUP($D2,rates,IF($E2=15%,8,IF($E2=30%,12,IF($E2="Default",4))),FALSE))