Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Accuracy | Excel Discussion (Misc queries) | |||
VLOOK-pivot table expanding want to update vlook automatically | Excel Worksheet Functions | |||
VLOOK-pivot table expanding want to update vlook automatically | Excel Worksheet Functions | |||
CHecking accuracy | Excel Discussion (Misc queries) | |||
Accuracy | Excel Programming |