VLookup

The VLOOKUP (Vertical lookup) function looks for a value in the leftmost column of a table, and then returns a value in the same row from another column you specify. You often have to insert a worksheet and give it a name. The code is build up as follows:

1. Application.VLOOKUP(lookup_value, table_array, column_index, range_lookup)

2. Application.WorksheetFunction.VLOOKUP(lookup_value, table_array, column_index, range_lookup)

Lookup_value: The value to search in the first column of the table array. Lookup_value can be a value or a reference. If lookup_value is smaller than the smallest value in the first column of table_array, VLOOKUP returns the #N/A error value.

Table_array: Two or more columns of data. Use a reference to a range or a range name. The values in the first column of table_array are the values searched by lookup_value. These values can be text, numbers, or logical values. Uppercase and lowercase text are equivalent. Col_index_num: The column number in table_array from which the matching value must be returned. A col_index_num of 1 returns the value in the first column in table_array; a col_index_num of 2 returns the value in the second column in table_array, and so on. If col_index_num is: Less than 1, VLOOKUP returns the #VALUE! error value. Greater than the number of columns in table_array, VLOOKUP returns the #REF! error value.

Range_lookup: A logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match: If TRUE or omitted, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned.

The values in the first column of table_array must be placed in ascending sort order; otherwise, VLOOKUP may not give the correct value. You can put the values in ascending order by choosing the Sort command from the Data menu and selecting Ascending.

If FALSE, VLOOKUP will only find an exact match. In this case, the values in the first column of table_array do not need to be sorted. If there are two or more values in the first column of table_array that match the lookup_value, the first value found is used. If an exact match is not found, the error value #N/A is returned.

date - speciaal moeilijk

VLookup is sensitive to data types. And the VBA routine is looking for a DATE data type, but such does not exist on an Excel worksheet. So you nee d to convert your string lookup_value to a data type compatible with what is on the worksheet. This could be a Long (for whole dates) or Double (if you are including times).

Result = Application.WorksheetFunction.VLookup(CLng(CDate(lookup_value)), lookup_table, 2, False)

no Vlookup function in VBA

there is no VBA Vlookup function, you can call the Excel Vlookup function from VBA. By use of the VBA Application property, any of the built-in Excel functions can be accessed in VBA, using the following command:

Result = Application.WorksheetFunction.Function Name ( function arguments ) The syntax for calling the VBA Vlookup function call is therefore:

Result = Application.WorksheetFunction.Vlookup ( lookup_value, table_array, col_index_num, [range_lookup] )

zoekterm NA

if the zoekterm is not present in the range then VLOOKUP throws a “1004 Error”.

*** looking for text

vraag: For example, if i have a column of numbers (formatted as text) like 100-4333-239 I have been removing the "-" and converting to a number and then doing a vlookup. Is there a way to look up text without all the converting? Yes, but... The common problem with vlookup and text data is Excel needs to think the reference and lookup list are of the same type. Especially when dealing with data that is just numbers, Excel gets confused sometimes. Look for the green triangles in the upper left of your cells. You need both data locations to either have them, or not have them. If it is mismatched vlookup will fail. The last argument determines whether VLOOKUP should find "approximate" matches as well (which is, in my opinion, a bad description -- passing TRUE there or leaving the argument out seems to tell Excel that the table is sorted). In practice, this means that you should pass FALSE when searching for text: The last argument for VLOOKUP is not whether it is sorted, but if you use true you will get unpredictable results if the table is not sorted. I know myself, I have never had a need to use TRUE in any of the many many vlookups I have typed over the years.

*** hyperlink =HYPERLINK("#"&ADDRESS(MATCH(E21,Sheet2!$A$1:$A$176),3,,,"sheet2"),VLOOKUP(E21,Sheet2!$A$1:$Y$176,3,FALSE))

*** veel voorkomende fouten -http://searchenginewatch.com/article/2334653/Excel-VLOOKUP-Basics-Top-5-Mistakes-Rookies-Make

1. Not Having Lookup_Value in First Column of Your Table Array

2. Counting the Wrong Number of Columns for Col_index_num

3. [Range_Lookup] Not Using FALSE for Exact Matching [default = true, mvr]

4. Forgetting Absolute References (F4) When Copying the Formula

5. Extra Spaces or Characters

6. It only returns the first match it finds, even if there are hundreds of possible matches.

7. It can only return a value in the table array to the right - it can't go left!

0. You Forgot to Reference Lock Your Arrays. Use the F4 Key to reference lock your arrays before dragging your formula down. - See more at: http://www.randomwok.com/excel/top-3-reasons-why-my-vlookup-isnt-working/#sthash.qYRCJkJz.dpuf it’s good practice to always reference lock your arrays when writing a VLOOKUP formula. - See more at: http://www.randomwok.com/excel/top-3-reasons-why-my-vlookup-isnt-working/#sthash.qYRCJkJz.dpuf ==== http://thinketg.com/say-goodbye-to-vlookup-and-hello-to-index-match/ limitations and dangers of vlookup The answer to these problems and limitations is the INDEX-MATCH lookup method. This methods uses two functions together to provide a more safe and flexible lookup feature. Here’s how each function works, independently: =INDEX(Return_value_range, MATCH(Lookup_value, Lookup_value_range, Match_type)) [opposite benadering; http://thinketg.com/extended-vlookup-function-for-excel/ ]


Sub VLookup_string()
Dim lookup_value As String: lookup_value = "b"
Dim lookup_table As Range
Dim ret
On Error GoTo HandleError

Set lookup_table = Worksheets("Sheet1").Range("A1:C8")
' ret = Application.VLookup(lookup_value, lookup_table, 2, False) 'both work
ret = Application.WorksheetFunction.VLookup(lookup_value, lookup_table, 2, False)

MsgBox ret

HandleExit:
    Exit Sub
HandleError: MsgBox "not found"
    MsgBox Err.Description
    Resume HandleExit

End Sub

Lookup number


Sub VLookup_number()
Dim lookup_value As Variant: lookup_value = 6
Dim lookup_table As Range
Dim ret
On Error GoTo HandleError

Set lookup_table = Worksheets("Sheet1").Range("A1:C8")
' ret = Application.VLookup(lookup_value, lookup_table, 2, False) 'both work
ret = Application.WorksheetFunction.VLookup(lookup_value, lookup_table, 2, False)

MsgBox ret

HandleExit:
    Exit Sub
HandleError: MsgBox "not found"
    MsgBox Err.Description
    Resume HandleExit

End Sub

Alternative

.Find - only search for data


    Cells.Find(What:="f", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _ xlPart, 
    SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, _ 
    SearchFormat:=False).Activate