Lookup values by row in a vertical table or range
=VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
Try using the new XLOOKUP function instead of VLOOKUP. XLOOKUP works in any direction (not just to the right) and returns exact matches by default (not approximate matches as with VLOOKUP).
The V in VLOOKUP stands for vertical. In a vertically organized table or range, return a value in the same row but a different column to the lookup_value using either exact or approximate matching.
VLOOKUP defines 'what to lookup', 'where to lookup', 'the column number to return' and 'whether to return an approximate or exact match'.
For example, =VLOOKUP("Bang*",B3:F12,3,0) will look for wildcard values that begin with "Bang" (e.g. Bangladesh) in column B (first column) and will then return the matching result from the 3rd column (column D) in the range B3:F12. The 0 (FALSE) indicates an exact match is required. #N/A is returned if there is no match. Use the IFNA function to trap and handle #N/A errors.
Use HLOOKUP to return a value in the same column but a different row to the lookup_value
VLOOKUP and exact searches.
In the example below, the VLOOKUP function is used to match geographic and population data to specific countries and perform calculations on population data over time.
Use the VLOOKUP function to lookup exact values by row in a vertical table or range.
VLOOKUP and approximate searches.
In the example below, the VLOOKUP function is used to match population data to specific ranges of data values.
Use the VLOOKUP function to lookup approximate values by row in a vertical table or range.
Syntax
=VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
The VLOOKUP function consists of at least 3 arguments:
- lookup_value. Required. The value to be looked for in the first column of a table or range.
- table_array. Required. The table or range across which to lookup the end result.
- col_index_num. Required. The column in the table or range from which to retrieve the end result.
- range_lookup. Optional. TRUE or 1 for an approximate lookup. FALSE or 0 for an exact lookup. If range_lookup is omitted, the VLOOKUP function will default to the closest approximate match. If range_lookup is TRUE (default and approximate), the lookup values in the first column of the table or range need to be in ascending order. If range_lookup is FALSE (exact), the lookup values in the first column do not to be sorted.
Try it now!
- Enter your formulas into the grey cells.
Scope
VLOOKUP defines 'what to lookup', 'where to lookup', 'the column number to return' and 'whether to return an approximate or exact match'.
|
=VLOOKUP("United States",B3:F12,4,FALSE) || result is a value from the 4th column in the data range B3:F12 which is an exact match to the lookup value "United States". |
|
=VLOOKUP(E6,mn_growth,2,TRUE) || result is a value from the 2nd column in the named range "mn_growth" which is an approximate match to the lookup value in E6 (in this case a number). |
|
=CONCAT(VLOOKUP("Bang*",B3:C8,2,FALSE),", ",VLOOKUP(F8,mn_growth,2,1)) || result is a concatenation of an exact match for a wildcard value from the first VLOOKUP and an approximate match for a number in the second VLOOKUP. |
Caveats
- VLOOKUP only looks to the right in a vertically organized table or range.
- lookup_value
- Data sets need to be organized so that the lookup_value appears in the first column of a vertical table or range. The values to be returned need to be in a column to the right of the lookup_value.
- Create a helper column in the first column of the table or range to join multiple fields (e.g. firstname AND lastname) together to enable the lookup_value to handle multiple criteria.
- VLOOKUP supports wildcards to perform partial matches on a lookup_value. The range_lookup argument must be exact (FALSE, 0) when using wildcards. The asterisk * matches any sequence of characters. The question mark ? matches any single character. To find an actual question mark or asterisk, type a tilde (~) in front of the character.
- Don't store numbers or date values as text, otherwise VLOOKUP might return erroneous values.
- Ensure data in the first column doesn't have leading spaces, trailing spaces, inconsistent use of straight (' or ") and curly (‘ or “) quotation marks, or nonprinting characters, otherwise VLOOKUP might return erroneous values. Use the CLEAN or TRIM function to clean up data.
- table_array
- Use absolute references for table_array to limit errors when copying or filling the formula.
- Sort the first column of the table_array when using a VLOOKUP for approximate matches (range_lookup is TRUE or 1).
- col_index_num
- a col_index_num greater than the number of columns in table_array will return a #REF! error
- Dynamic col_index_num values are possible using the MATCH function.
- range_lookup
- The range_lookup argument in VLOOKUP defaults to TRUE or 1 which uses approximate matching. This can lead to erroneous results, so use carefully or use FALSE or 0 to ensure exact matches are returned.
- Approximate matching (TRUE, 1) is useful when placing numbers within a range, e.g. placing the value 2.3% into a range 0-5%. This prevents the need to having to lookup every iteration of a number. Data must be sorted in ascending order when using the approximate lookup argument.
- Exact matches (FALSE, 0) will return the first match if there are duplicate values.
- #N/A is returned if there is no match. Use the IFNA function to trap and handle #N/A errors. The #N/A error is an indication of missing or misspelt values, incorrect ranges or that the range_lookup is set to exact instead of approximate.
Common issues
- Incorrect value returned: If range_lookup is TRUE or omitted, the first column needs to be sorted alphabetically or numerically, otherwise the return value might be incorrect. Either sort the first column, or use FALSE for an exact match.
- #N/A: If range_lookup is TRUE or omitted, the lookup_value is smaller than the smallest value in the table_array.
- #N/A: If range_lookup is FALSE, an exact match can not be made.
- #REF!: col_index_num is likely greater than the number of columns in table_array.
- #VALUE!: table_array is less than 1.
- #NAME?: text values might be missing quotations.
- #SPILL!: lookup_value is likely set as an entire column (C:C) rather than a cell reference (C2).
Related Functions
- HLOOKUP to lookup values by column in a horizontal table or range.
- INDEX and MATCH to lookup values in any direction.
- OFFSET to lookup values in any direction.
- XLOOKUP to lookup values in any direction.
Functions Category