excel vlookup with if functionc

Question

For example:

F2 = VLOOKUP($L$2,sheet2!$A$2:$G$169,6,0)

I would like to add in the function that if $L$2 can not be found in the sheet2!$A$2:$G$169, then show "invalid".

May i know how should i modified to code in excel? Thanks.

Answer 1

First test what gets returned if $L$2 cannot be found by putting in a value you know is not in the search set. For example if it returns 0 then:

=IF(VLOOKUP($L$2,sheet2!$A$2:$G$169,6,0) = 0, "invalid", VLOOKUP($L$2,sheet2!$A$2:$G$169,6,0))

If it returns an error then try something along the lines of

=IF(ISERROR(VLOOKUP($L$2,sheet2!$A$2:$G$169,6,0)), "invalid", VLOOKUP($L$2,sheet2!$A$2:$G$169,6,0))

or just

=IFERROR(VLOOKUP($L$2,sheet2!$A$2:$G$169,6,0), "invlaid")