[color red]Microsoft Excel: VLOOKUP with ERROR.TYPE = 7

[GQSMOOTH]

I am using the following formula in my spreadsheet calculation.

=IF(ERROR.TYPE((VLOOKUP(E3,Everything_5s,6,FALSE)/$M3))=7,0,(VLOOKUP(E3,Everything_5s,6,FALSE)/$M3))


Basically, I want to be able to display, 0 or the actual value based on two scenarios. If the value does not exist in my VLOOKUP I get the "N/A" from Excel and I want to be able to convert this to 0. If the value does exist in my VLOOKUP then I want it to display the actual value.

My problem is that when I have an actual value to display the formula displays "N/A". It works fine if the value is "N/A" but not if there is an actual value. Any Help?!?!!

 

[xlbo]

This should do it:

=IF(ISNA(VLOOKUP(E3,Everything_5s,6,FALSE)),0,(VLOOKUP(E3,Everything_5s,6,FALSE)/$M3))
HTH
Rgds
~Geoff~

 

[GQSMOOTH]

Many Thanks. Should have looked for another Excel function to handle the N/A. Works like a champ.

GQ$MOOTH