VLOOKUP #N/A when no criteria entered

[nroy]

Hi,

Can anyone help with this VLOOKUP problem?

Column A is where I will enter my lookup criteria - a 5-digit code
Column B contains my lookup formula - returning a monitory value when the code is entered. The formula is =VLOOKUP(A2,data2010,2,FALSE)

Cells B2 - B6 contain the lookup formula but show #N/A when no code has been entered in cells A2 - A6. I would like the cell to appear blank when no code has been entered as I will not always need to uses all rows.

Cell B7 is is my total formula =SUM(B2:B6) This returns a #N/A if there are any #N/A errors in column B.

I would like to stop the #N/A errors appearing in column B and for the total amount to show in cell B7.

I hope this makes sense!

Many thanks,
Nicola

 

[CharlesChuckieCharles]

Try this, it detects #N/A and replaces with Blank

Use where you currently have VLOOKUP(A2,data2010,2,FALSE)

=IF(ISNA(VLOOKUP(A2,data2010,2,FALSE),"",VLOOKUP(A2,data2010,2,FALSE))

 

[ChrisKingston]

Try this. It is a little shorter. =IFERROR(VLOOKUP(A2,data2010,2,FALSE),"")

 

[CharlesChuckieCharles]

Try this. It is a little shorter. =IFERROR(VLOOKUP(A2,data2010,2,FALSE),"")

Be Aware that IFERROR masks all of The following error types : #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!.

not Just #N/A so stick with ISNA if you need to spot other errors

 

[Aladin Akyurek]

Hi,

Can anyone help with this VLOOKUP problem?

Column A is where I will enter my lookup criteria - a 5-digit code
Column B contains my lookup formula - returning a monitory value when the code is entered. The formula is =VLOOKUP(A2,data2010,2,FALSE)

Cells B2 - B6 contain the lookup formula but show #N/A when no code has been entered in cells A2 - A6. I would like the cell to appear blank when no code has been entered as I will not always need to uses all rows.

Cell B7 is is my total formula =SUM(B2:B6) This returns a #N/A if there are any #N/A errors in column B.

I would like to stop the #N/A errors appearing in column B and for the total amount to show in cell B7.

I hope this makes sense!

Many thanks,
Nicola

B2:

=IF(B2="","",VLOOKUP(A2,...))

Even better:

=IF(B2="","",IFERROR(VLOOKUP(A2,...),""))

 

[nroy]

Try this, it detects #N/A and replaces with Blank

Use where you currently have VLOOKUP(A2,data2010,2,FALSE)

=IF(ISNA(VLOOKUP(A2,data2010,2,FALSE),"",VLOOKUP(A2,data2010,2,FALSE))

Thanks, I've tried this but it shows and error highlighting the "" part and is asking or an ISNA value.

 

[nroy]

Thanks, this formula returns an #name? error - any ideas?

 

[nroy]

Try this. It is a little shorter. =IFERROR(VLOOKUP(A2,data2010,2,FALSE),"")

Thanks, this formula returns an #name? error - any ideas?

 

[CharlesChuckieCharles]

Thanks, I've tried this but it shows and error highlighting the "" part and is asking or an ISNA value.

I missed a bracket

=IF(ISNA( VLookup(.......)),"", Vlookup(....))

 

[Aladin Akyurek]

Thanks, this formula returns an #name? error - any ideas?

Try just...

B2:

=IF(A2="","",VLOOKUP(A2,...))