Vlookup is one of the most popular look up functions in Excel. When you need to extract data from a table based on a particular value, you can use Vlookup to do that.
In simple terms, it looks at a table in the first column for a specific value and returns a cell in the same row where you choose the column to return from. You get to choose whether you want to find an approximate match or an exact match (more on which one to choose later).
You can use this for both text searches as well as numeric searches, which proves to be a powerful function and can easily help you out in your everyday tasks at work.
The Vlookup function has the form of
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup = TRUE])
> LOOKUP_VALUE: The number or text sought for.
> TABLE_ARRAY: A cell reference of the table range. Note: Do NOT include headers in your cell reference if you are searching for text and an approximate match. Trust me, you won’t get the desired results in some cases (more on that later).
> COL_INDEX_NUM: The number of the column you want to return. This number is based on the table you pass, not by the column character’s number equivalent. For example, if your range is B2:D5, column 1 is B, column 2 is C, and column 3 is D.
> RANGE_LOOKUP: Can either be TRUE or FALSE. It s an optional parameter where if left out, the value is set to TRUE. If TRUE, the function will look for an approximate match. This means that for a numerical search, it will return the closest number smaller than the value you’re looking for, if it does not find an exact match. If FALSE, the function will look only for an exact match. And if no exact match is found, an # NA ! error will be returned. I should note that if you choose FALSE for range_lookup, the first column in the table must be sorted from smallest to largest (or for text, alphabetical). TRUE can be represented by “1” and FALSE by “0”
Also read
Why INDEX-MATCH Is Far Better Than VLOOKUP or HLOOKUP in Excel
How to Decide Which Excel Lookup Formula to Use