VLookUp and If

[Sparta_leadhead]

Hey guys.

I have a Vlookup as follows:

=VLOOKUP(M5,'Hits on Portal'!A4:D145,3,FALSE)

This searches for a certain name in a list, and returns a value in column 3 (a number), which works fine.

The problem comes when the name im looking for is not in the table the Vlookup searches, and returns #N/A.

How can i nest an IF fucntion to the formula above so that if the value returned from the Vlookup is #N/A, it is change to 0.

Thanks in advance

 

[DonkeyOte]

You could either reiterate the lookup with an ISNA/ISERROR check or use a Match test in first instance (so only referencing one column)

=IF(ISNUMBER(MATCH(M5,'Hits on Portal'!A4:A145,0)),VLOOKUP(M5,'Hits on Portal'!A4:C145,3,FALSE),0)

Note as you're returning C from your range no need to reference D in the range.

HTH

 

[Sparta_leadhead]

That worked a treat.

I spent all morning trying to nest an "IF" in to it.

Thanks for your help