Vlookup Question

[inahaze72]

Is there a way to have the Vlookup formula return a 0 value if there is not a match. Currently, if the formula doesn't find a match, it returns an output of #N/A. I need to convert it to a numeric value.

Formula I'm using:
=VLOOKUP(A2,'page1'!$A$1:$B$3795,2,FALSE)

thanks

 

[eliW]

On 2002-04-08 10:38, inahaze72 wrote:
Is there a way to have the Vlookup formula return a 0 value if there is not a match. Currently, if the formula doesn't find a match, it returns an output of #N/A. I need to convert it to a numeric value.

Formula I'm using:
=VLOOKUP(A2,'page1'!$A$1:$B$3795,2,FALSE)

thanks

Look at this:
http://www.mrexcel.com/board/viewtopic.php?topic=4277&forum=2
Eli

 

[Mark W.]

On 2002-04-08 10:38, inahaze72 wrote:
Is there a way to have the Vlookup formula return a 0 value if there is not a match. Currently, if the formula doesn't find a match, it returns an output of #N/A. I need to convert it to a numeric value.

Formula I'm using:
=VLOOKUP(A2,'page1'!$A$1:$B$3795,2,FALSE)

thanks

Not with a single VLOOKUP. Some will suggest testing the results of a VLOOKUP and then returning "" if it's #N/A; otherwise perform another VLOOKUP. This is very inefficient when all you really need to do is return the VLOOKUP results to a cell [hidden, if desire] (e.g., A2) and then test those results in another using =IF(ISNA(A2),"",A2).
This message was edited by Mark W. on 2002-04-08 10:50

 

[Albert 1]

=IF(ISNA(VLOOKUP(A2,'page1'!$A$1:$B$3795,2,FALSE));0;VLOOKUP(A2,'page1'!$A$1:$B$3795,2,FALSE))
This message was edited by Albert 1 on 2002-04-08 10:54