Vlook accuracy
 

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

Vlook accuracy
 

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

-----Original Message-----
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
.

Vlook accuracy
 

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


-----Original Message-----
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
.

Vlook accuracy
 

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",V LOOKUP(123,A1:B10,2,FALSE)
)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Gordon Cartwright" wrote in message
...
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