Vlook accuracy

[Gordon Cartwright]

Hi...

I need a vlookup formula/code that will return an EXACT
value or an error message telling the user that the number
they've enetered doesn't exist...

Help in any form welcome!

GC

 

[Patrick Molloy]

VLOOKUP does return an errorif an excat value is sought
but doesn't exist - just be sure that the Range_Lookup
value is set to False

In a worksheet you'd need to use an IF statement
eg
=IF(ISNA(VLOOKUP(E7,K6:L80,2,FALSE)),"NO MATCH",VLOOKUP
(E7,K6:L80,2,FALSE))

This return the 2 column in the table if there's a match,
otherwise returns the message.

In VBA, use "Application.WorksheetFunction.VLookup"

- but do it in a function where you can trap the error
eg

Sub LookupTest()

MsgBox GetValue(Range("E7"), Range("K4:L800"), 2)

End Sub

Function GetValue(Target As String, _
table As Range, _
Col As Long) As Variant
Dim result As Variant
On Error Resume Next
result = _
Application.WorksheetFunction.VLookup(Target, table,
Col, False)
If Err.Number <> 0 Then
Err.Clear
result = "No Match"
End If
GetValue = result
On Error GoTo 0
End Function

Patrick Molloy
Microsoft Excel MVP

 

[Guest]

Hi

Use vlookup(lookup value, table array, index number, 0)

The zero returns a false value which means it has to be an
exact match.

Ed

 

[Chip Pearson]

Gordon,

To ensure an exact match in a VLOOKUP, set the last argument to FALSE.
E.g.,

=VLOOKUP(123,A1:B10,2,FALSE)

To display an error message if not found, use something like

=IF(ISERROR(VLOOKUP(123,A1:B10,2,FALSE)),"Error",VLOOKUP(123,A1:B10,2,FALSE)
)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)