VLOOKUP NESTED

[Tester]

Hi - this is my formula but it returns N/A. Where have I gone wrong please?

=IF(I62>0,I62*(VLOOKUP((VLOOKUP(C62,$J$43:$M$56,2,TRUE)),'Control
Sheet'!$F$29:$G$31,2,TRUE)),0)

Basically, if I62 has a value then i want to multiply it by a varaible
percentage which is determined by the vlookup formula. The nested Vlookup
finds the value against the name in C62 to use in Control Sheet to determine
the percentage rate.

TIA
Chris

 

[Ronald Dodge]

I have ran into the same issues years ago, so what I had to do was wrap the
lookup function within an ISERROR function for the conditional statement,
which if the ISERROR function returns True, then perform something else such
the 0 in this case, and the false would perform the lookup function. The
downfall to this, it's having to perform the lookup 2 times, which I use the
MATCH function quite a bit. All of the different lookup functions has this
same basic problem with it, but it's best to have this sort of issue, as you
would rather know something isn't in the list than not to have a way of
knowing it's not in the list.

=IF(ISERROR(VLOOKUP(C62,$J$43:$M$56,2,TRUE)),0,IF(ISERROR(VLOOKUP(VLOOKUP(C62,$J$43:$M$56,2,TRUE),'Control
Sheet'!$F$29:$G$31,2,TRUE)),0,I62*VLOOKUP(VLOOKUP(C62,$J$43:$M$56,2,TRUE),'Control
Sheet'!$F$29:$G$31,2,TRUE)))

Ronald R. Dodge, Jr.
Master MOUS 2000