VLOOKUP Problem

[Henry G.]

In cell B2 have written the formula =VLOOKUP(A1,TABLE1,2,FALSE)

When there is no match for cell A1 in the first column of “table1”, the formula yields “#N/A”. I want to write a formula in cell B2 that will yield zero when no match is found and the value of column 2 in “table1” when there is a match.
THANKS . . .
Henry G.

 

[Aladin Akyurek]

On 2002-10-21 09:57, Henry G. wrote:
In cell B2 have written the formula =VLOOKUP(A1,TABLE1,2,FALSE)

When there is no match for cell A1 in the first column of “table1”, the formula yields “#N/A”. I want to write a formula in cell B2 that will yield zero when no match is found and the value of column 2 in “table1” when there is a match.
THANKS . . .
Henry G.

Two fast options...

[1]

=IF(ISNA(VLOOKUP(A1,TABLE1,2,0))),0,GETV())

which requires that you download & install the morefunc.xll add-in, from

http://longre.free.fr/english/index.html

[2]

=IF(ISNUMBER(MATCH(A1,INDEX(TABLE1,0,1),0)),VLOOKUP(A1,TABLE1,2,0),0)

 

[jimboy]

try this;

=IF(ISERROR(VLOOKUP(A1,table,1,0)),0,VLOOKUP(A1,table,2,0))

 

[Mark W.]

On 2002-10-21 09:57, Henry G. wrote:
In cell B2 have written the formula =VLOOKUP(A1,TABLE1,2,FALSE)

When there is no match for cell A1 in the first column of “table1”, the formula yields “#N/A”. I want to write a formula in cell B2 that will yield zero when no match is found and the value of column 2 in “table1” when there is a match.
THANKS . . .
Henry G.

Why not add the needed records to your table to prevent the return of #N/A, or use an IF function to test the lookup_value and return 0 rather then performing a VLOOKUP?