VLookup - return 0 instead of #NA

[Ryanaj]

Looking for help on having the following function (If, or & VLookup) to return 0 instead of #NA when the Vlookup value is not present in sheet 1. I have seen iserror but cant seem to embed it correctly.

=IF(OR(AC17="S",AC17="LG",AC17="NL",AC17="SAG"),"0",VLOOKUP(A17,'F:\[BDP.xls]Sheet1'!$A$1:$T$65536,13,FALSE))


Thank you to all who provided support on query

[martindwilson]

=IF(OR(AC17="S",AC17="LG",AC17="NL",AC17="SAG"),"0",IF(ISNA(VLOOKUP(A17,'F:\[BDP.xls]Sheet1'!$A:$T,13,FALSE)),0,VLOOKUP(A17,'F:\[BDP.xls]Sheet1'!$A:$T,13,FALSE)))
or slightly shorter
=IF(OR(AC17={"S","LG","NL","SAG"}),"0",IF(ISNA(VLOOKUP(A17,'F:\[BDP.xls]Sheet1'!$A:$T,13,FALSE)),0,VLOOKUP(A17,'F:\[BDP.xls]Sheet1'!$A:$T,13,FALSE)))

[shg]

Maybe

=IF(OR(AC17={"S","LG","NL","SAG"}), 0, IFERROR(VLOOKUP(A17, 'F:\[BDP.xls]Sheet1'!$A$1:$T$65536, 13, FALSE), 0))

[Richard Buttrey]

Hi,

=IF(OR(AC17="S",AC17="LG",AC17="NL",AC17="SAG",ISERROR(VLOOKUP(A17,'F:\[BDP.xls]Sheet1'!$A$1:$T$65536,13,FALSE))),"0",VLOOKUP(A17,'F:\[BDP.xls]Sheet1'!$A$1:$T$65536,13,FALSE))

[TMS]

Untested:

=IF(OR(AC17="S",AC17="LG",AC17="NL",AC17="SAG"),"0",IF(ISNA(VLOOKUP(A17,'F:\[BDP.xls]Sheet1'!$A$1:$T$65536,13,FALSE),0,VLOOKUP(A17,'F:\[BDP.xls]Sheet1'!$A$1:$T$65536,13,FALSE))


Regards, TMS