Handling VLOOKUP errors gracefully

[nbannon]

Is there any way that you can handle a VLOOKUP error better that it returning #NA.

Basically, I have a spreadsheet that sucks in info from a database, then collates the info into a table using a VLOOKUP, then loops through the resulting table to create an array. At times, the VLOOKUP will not find the value it is looking for, but I need it to return an empty string in the cell rather than the #NA value, which when creating the array causes an error.

Does that make any sense at all ?

Any help would be great.

Cheers

Noel.

 

[Andrew Poulsom]

The usual way is:

=IF(ISNA(VLOOKUP(BlahBlah)),0,VLOOKUP(BlahBlah))

 

[WillR]

Here's a bit of VB I picked up somewhere that you can paste in to your personal.xls workbook and run for any worksheet - it will place the IF(ISERR.... formula around all formulas in the worksheet...getting rid of Nasty errors....
*****************************************
Sub ErrorTrap()

' Adds =If(IsError(xxx)) around formulas
' results in no error values on speadsheet

Dim cel As Range
Dim rng As Range
Dim Check As String

Const Equ As String = "=IF(ISERROR(_x) ,"""", _x)"

Check = Left$(Equ, 12) & "*" ' Check for =IF(ISERROR(

On Error Resume Next

Set rng = Selection.SpecialCells(xlFormulas, 23)
If rng Is Nothing Then Exit Sub

With WorksheetFunction
For Each cel In rng
If Not cel.Formula Like Check Then
cel.Formula = .Substitute(Equ, "_x", Mid$(cel.Formula, 2))
End If
Next
End With
End Sub
*********************************************

 

[Aladin Akyurek]

On 2002-09-11 00:46, nbannon wrote:
Is there any way that you can handle a VLOOKUP error better that it returning #NA.

Basically, I have a spreadsheet that sucks in info from a database, then collates the info into a table using a VLOOKUP, then loops through the resulting table to create an array. At times, the VLOOKUP will not find the value it is looking for, but I need it to return an empty string in the cell rather than the #NA value, which when creating the array causes an error.

Does that make any sense at all ?

Any help would be great.

Cheers

Noel.

I beg to differ...

A more efficient option is:

=IF(ISNUMBER(MATCH(lookup-value,INDEX(LTable,0,1),0)),VLOOKUP(lookup-value,LTable,ColIdx,0),"")

Another, if you installed morefunc.xll (an add-in)...

=IF(ISNA(SETV(VLOOKUP(lookup-value,LTable,ColIdx,0))),"",GETV())
This message was edited by Aladin Akyurek on 2002-09-11 03:11