Today, we’ll explore how to use VLOOKUP in Excel with detailed, step-by-step examples. You’ll learn how to perform a VLOOKUP from another sheet and workbook, search using wildcards, and much more. This article kicks off a series dedicated to VLOOKUP, one of the most powerful yet complex and often misunderstood Excel functions. We’ll aim to explain the basics in simple terms to ease the learning process for beginners. Additionally, we’ll provide formula examples illustrating the most common uses of VLOOKUP in Excel, making the content both informative and enjoyable.
Excel VLOOKUP Function
What is VLOOKUP?Â
VLOOKUP is a function in Excel that searches for a specified value and returns a corresponding value from another column. More precisely, it looks up a value in the first column of a specified range and retrieves a value from another column in the same row.
Common Usage
Typically, VLOOKUP searches a data set based on a unique identifier and fetches the information linked to that identifier.
Understanding the “V”
The “V” in VLOOKUP stands for “vertical,” distinguishing it from HLOOKUP, which looks up values in a row (where “H” stands for “horizontal”).
AvailabilityÂ
This function is available in all versions from Excel 2007 through Excel 365.
Tip:For users of Excel 365 and Excel 2021, the XLOOKUP function is available as a more flexible and powerful alternative to VLOOKUP.
VLOOKUP syntax
The syntax for the function is as follows:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Where:
Lookup_value (required): This is the value you want to search for. It can be a number, date, text, cell reference (a reference to a cell containing the lookup value), or the result of another function. Text values should always be enclosed in double quotes.
Table_array (required): This is the range of cells where you will search for the lookup value and retrieve a match. VLOOKUP always looks in the first column of the table array, which may include text, numbers, dates, and logical values.
Col_index_num (required): This is the number of the column from which the value will be returned. The count starts from the leftmost column in the table array, beginning with 1.
Range_lookup (optional): This determines whether the search is for an approximate or exact match:
TRUE or omitted (default): Searches for an approximate match. If an exact match isn’t found, the formula looks for the largest value smaller than the lookup value. The lookup column needs to be sorted in ascending order.
 FALSE: Searches for an exact match. If an exact match isn’t found, the formula returns a #N/A error.
Basic VLOOKUP Formula
Here is an example of the Excel VLOOKUP formula in its simplest form. Please take a look at the formula below and try to “translate” it into plain English:
=VLOOKUP(“Book”, A2:B11, 2, FALSE)
– The 1st argument (`lookup_value`) indicates that the formula is searching for the word “Book”.
– The 2nd argument (`table_array`) is `A2:B11`. Since the search is performed in the left-most column, this means the formula looks for “lion” in the range `A2:A11`.
– The 3rd argument (`col_index_num`) is `2`. This means the formula will return a value from the second column in the specified table array, which is column B.
– The 4th argument (`range_lookup`) is `FALSE`, indicating that an exact match is required.
Putting it all together, the formula searches for “book” in the range `A2:A11`, finds an exact match, and returns the corresponding value from column B in the same row.
For convenience, you can type the value you are interested in into a cell, such as `E1`, and replace the hardcoded text with this cell reference to make the formula look up any value you input in `E1`:
=VLOOKUP(E1, A2:B11, 2, FALSE)
Excel VLOOKUP – 5 Key Points to Remember!
- VLOOKUP Searches Right Only: The VLOOKUP function searches in the leftmost column of the table array and returns a value from a column to the right. It cannot look to its left. To pull values from columns to the left, use the INDEX MATCH (or INDEX XMATCH in Excel 365) combination, which is not restricted by column positioning.
- Case Insensitivity: VLOOKUP is case-insensitive, meaning it treats uppercase and lowercase characters as equivalent. If you need to distinguish between letter cases, use case-sensitive VLOOKUP formulas.
- The Importance of the Last Parameter: The last parameter in VLOOKUP is crucial. Use TRUE for an approximate match and FALSE for an exact match. For more details, see VLOOKUP TRUE vs. FALSE.
- Data Sorting for Approximate Matches: When searching for an approximate match, ensure that the data in the lookup column is sorted in ascending order.
- Handling #N/A Errors: If the lookup value is not found, VLOOKUP returns a #N/A error. For information about other possible errors, see Why Excel VLOOKUP is not working.
For More: Microsoft Excel
