Excel VLOOKUP Function
The Excel VLOOKUP function searches for a specific value in the first column of the selected range (table) and returns a value that resides in the same row as the lookup value from a specific column
Example: Excel VLOOKUP Function
|
=VLOOKUP(502,$B$5:$D$9,2,FALSE)
|
Result in cell E12 ($1.50) - searches for the exact value of 502 in range ($B$4:$B$9) and returns the value that resides in the same row as the lookup value from column 2 (column C) of the selected range.
|
|
=VLOOKUP(400,$B$5:$D$9,2,TRUE)
|
Result in cell E13 ($5.40) - searches for the approximate value of 400 in range ($B$5:$B$9) and returns the value that resides in the same row as the lookup value from column 2 (column C) of the selected range.
|
|
=VLOOKUP(254,$B$5:$D$9,3,FALSE)
|
Result in cell E14 ($4.50) - searches for the exact value of 254 in range ($B$5:$B$9) and returns the value that resides in the same row as the lookup value from column 3 (column D) of the selected range.
|
METHOD 2. Excel VLOOKUP Function using links
EXCEL
|
=VLOOKUP(B12,$B$5:$D$9,C12,D12)
|
Result in cell E12 ($1.50) - searches for the exact value of 502 in range ($B$5:$B$9) and returns the value that resides in the same row as the lookup value from column 2 (column C), of the selected range.
|
|
=VLOOKUP(B13,$B$5:$D$9,C13,D13)
|
Result in cell E13 ($5.40) - searches for the approximate value of 400 in range ($B$5:$B$9) and returns the value that resides in the same row as the lookup value from column 2 (column C) of the selected range.
|
|
=VLOOKUP(B14,$B$5:$D$9,C14,D14)
|
Result in cell E14 ($4.50) - searches for the exact value of 254 in range ($B$5:$B$9) and returns the value that resides in the same row as the lookup value from column 3 (column D) of the selected range.
|
METHOD 3. Excel VLOOKUP function using the Excel built-in function library with hardcoded values
EXCEL
| =VLOOKUP(502,$B$5:$D$9,2,FALSE) Note: in this example we are populating all of the VLOOKUP function arguments using hardcoded values. |
 |
METHOD 4. Excel VLOOKUP function using the Excel built-in function library with links
EXCEL
| =VLOOKUP(B12,$B$5:$D$9,C12,D12) Note: in this example we are populating all of the VLOOKUP function arguments using links. |
 |
Dim ws As Worksheet
ws.Range("E12").Value = WorksheetFunction.VLookup(502, ws.Range("$B$5:$D$9"), 2, False)
ws.Range("E13").Value = WorksheetFunction.VLookup(400, ws.Range("$B$5:$D$9"), 2, True)
ws.Range("E14").Value = WorksheetFunction.VLookup(254, ws.Range("$B$5:$D$9"), 3, False)
End Sub
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
Worksheet Name: Have a worksheet named VLOOKUP.
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell references ("E12"), ("E13") and ("E14") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
METHOD 2. Excel VLOOKUP function using VBA with links
VBA
Dim ws As Worksheet
ws.Range("E12").Value = WorksheetFunction.VLookup(ws.Range("B12"), ws.Range("$B$5:$D$9"), ws.Range("C12"), ws.Range("D12"))
ws.Range("E13").Value = WorksheetFunction.VLookup(ws.Range("B13"), ws.Range("$B$5:$D$9"), ws.Range("C13"), ws.Range("D13"))
ws.Range("E14").Value = WorksheetFunction.VLookup(ws.Range("B14"), ws.Range("$B$5:$D$9"), ws.Range("C14"), ws.Range("D14"))
End Sub
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
Worksheet Name: Have a worksheet named VLOOKUP.
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell references ("E12"), ("E13") and ("E14") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
The Excel VLOOKUP function searches for a specific value in the first column of the selected range (table) and returns a value that resides in the same row as the lookup value from a specific column.
=VLOOKUP(lookup_value, table, column_index_number, [range_lookup])
lookup_value: (Required) The value you want to lookup in the first column from the selected range (table).
table: (Required) A table of data from which to lookup the value.
column_index_number: (Required) A column number in the table from which to source the data.
range_lookup: (Optional) A choice of TRUE or FALSE. Selecting TRUE will lookup an approximate match. Selecting FALSE will lookup an exact match.
ADDITIONAL NOTES
Note 1: The default range_lookup argument is TRUE (approximate match).
Note 2: If you have selected TRUE (approximate match) as the range_lookup value, the VLOOKUP function will initially search for the exact match. If the function cannot find an exact match the function will return the next smaller value. However, if your lookup values are not sorted in an ascending order this may return an incorrect value.
Note 3: The VLOOKUP function will return any value.
Note 4: The column_index_number argument cannot be below 1. If you have entered a value of less than 1 the VLOOKUP function will return an error (#VALUE!).
Note 5: The column_index_number argument cannot be greater than the number of columns in the selected range (table). If you have entered a value that is greater than the number of columns in the table the VLOOKUP function will return an error (#REF!).