Vlookup and If Statement

[dneas]

Is there anyway to write just ONE formula to get 0 when the #N/A is a result in the Vlookup?
Here is my Example:

Column H
=IF(VLOOKUP(A2,Sheet1!$A$4:$B$38,2,0),"0",VLOOKUP(A2,Sheet1!$A$4:$B$38,2,FALSE))

Column I
=ISNA(H2)

Column J
=IF(I2=TRUE,"0",H2)

 

[PaddyD]

if you're comfortable with vlookups in general, the following thread should give you some ideas:

http://mrexcel.com/board/viewtopic.php?topic=9893&forum=2

note especially the formulas Aladin uses in his spreadsheet simulation & which ones he recommends.

Post back if you need more info,

Paddy

 

[Aladin Akyurek]

On 2002-09-01 15:47, dneas wrote:
Is there anyway to write just ONE formula to get 0 when the #N/A is a result in the Vlookup?
Here is my Example:

Column H
=IF(VLOOKUP(A2,Sheet1!$A$4:$B$38,2,0),"0",VLOOKUP(A2,Sheet1!$A$4:$B$38,2,FALSE))

Column I
=ISNA(H2)

Column J
=IF(I2=TRUE,"0",H2)

You should have:

In H2: =VLOOKUP(A2,Sheet1!$A$4:$B$38,2,0)

In I2: =IF(ISNA(H2),0,H2)

Note. I replaced FALSE with 0 which means the same thing to Excel.

The foregoing is one way to avoid #N/A. Other, single formula, alternatives are discussed in the thread Paddy quotes.

An additional option is:

=IF(ISNA(SETV(VLOOKUP(A2,Sheet1!$A$4:$B$38,2,0))),"",GETV())

if you and your users have installed the morefunc add-in, available from:

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

 

[steffanb]

For the #na issue, I usually use IFERROR.
IE: =IFERROR(VLOOKUP,RANGE,COLUMN,FALSE),0)