[AccessD] OT: Excel Function - VLOOKUP

Hi Joe

We use this generic function to look up values in a range:

Function RLookup(ByVal rngLookup As Range, _
                 ByVal strField As String, _
                 Optional intSearchOrder As Integer = xlByRows, _
                 Optional intOffset As Integer = 1) As Variant

' Looks up a value in the named range rngLookup from the cell,
' offset intOffset rows below or columns right from the found cell.
'
' 2000-07-30.
' Cactus Data ApS, Gustav Brock

' Default intSearchOrder is xlByRows, as named ranges built this way
' are easily attached by Access.
  
  Dim rng       As Range
  Dim intRow    As Integer
  Dim intColumn As Integer
  
  ' No special errorhandling.
  On Error Resume Next

  If intSearchOrder = xlByRows Then
    ' Search by rows.
    ' Read from found row, offset intOffset rows, and found column.
  Else
    ' Search by columns.
    ' Read from found row and found column, offset intOffset columns.
    intSearchOrder = xlByColumns
  End If
  
  With rngLookup
    ' Start search from upper left cell in range by starting in lower right cell.
    ' Search case sensitive for whole words in values only.
    Set rng = .Find(strField, .Cells(.Rows.Count, .Columns.Count), xlValues, xlWhole, intSearchOrder, , True)
  End With
  If Not rng Is Nothing Then
    ' Searched value found.
    ' Lookup value to retrieve.
    With rng
      intRow = .Row - (intOffset * (intSearchOrder = xlByRows))
      intColumn = .Column - (intOffset * (intSearchOrder = xlByColumns))
      ' Return value, offset intOffset rows or columns from cell with found value.
      RLookup = .Worksheet.Cells(intRow, intColumn).Value
    End With
    Set rng = Nothing
  End If

End Function

You may be able to modify (simplify) it for your need.

/gustav

>>> JRojas at tnco-inc.com 26-04-2006 19:51:40 >>>
Hi All,

I have an Excel question.

The VLOOKUP function returns "#N/A" if it cannot find a matching value.
I was trying to wrap the VLOOKUP function in an IF statement to test if
it returns #N/A so I could output a zero instead of #N/A.

Does anyone know exactly what value to test for? I tried "#N/A", '#N/A',
#N/A, and FALSE but none these worked.

Thanks,
Joe Rojas
IT Manager
TNCO, Inc.
781-447-6661 x7506
jrojas at tnco-inc.com