Do you know about Excel lookup functions? What are they and for which purpose they are used? They are three types of Lookup functions in Excel. Those are Lookup, Hlookup and Vlookup. These are regarded as the most useful functions that have been supplied by Excel. But many users find it difficult to execute them as Lookup and Vlookup functions often get mixed up. How to use Vlookup in excel?
What do you mean by Lookup and Vlookup function?
The Lookup function is one of the three lookup and reference functions. The Lookup is used to return a value from a selected range of data arranged in ascending order in rows & columns. Lookup-value is a value that will be searched. Lookup-vector can be a single column or row that contains data arranged in ascending order.
When you’re selecting a Lookup function, one of the major considerations that you have to do is to know whether your data is arranged in row (horizontally) or in columns (vertically). If the data is organized horizontally, you can use Lookup or Hlookup function. If the data is organized vertically, you will use Vlookup function. It’s an Excel function that is used to lookup data in a table which is organized vertically. Vlookup tends to support approximate as well as exact matching. Lookup values will appear in the 1st column of the table passed by Vlookup.
- Purpose
Lookup a value in the table by matching on 1st column
- Return value
The matched value from the table
- Syntax
=VLOOKUP (value, table, col_index, [range_lookup])
Where
Value – The value to look for in 1st column of the table.
table – The table from that we have to retrieve a value.
col_index – The column in table from that we have to retrieve a value.
=VLOOKUP (value, table, col_index) : approximate match (default)
=VLOOKUP (value, table, col_index, TRUE): approximate match
=VLOOKUP (value, table, col_index, FALSE): exact match
How to use Vlookup in excel?
Step 1: Identify the column of cells that you want to fill with the new data.
Step 2: Select a ‘Function’ (Gx) > VLOOKUP and insert this formula in the highlighted cell.
Step 3: Enter the lookup value for which you will retrieve the new data.
Step 4: Enter table array of the spreadsheet where the desired data is located.
Step 5: Enter the column number of the data you want Excel to return.
Step 6: Enter the range lookup to find the exact or for finding the approximate match of lookup value.
Step 7: Click ‘Done’ and fill the new column.
Vlookup function also uses the wildcards that will help to perform the partial match on the lookup value. For example, you use Vlookup for retrieving values from the table after typing in one part of the lookup value. For using wild cards in Vlookup, you have to specify the exact match by giving 0 or False for last argument, range_lookup. The major difference between array form of lookup function and Vlookup function is that-
Vlookup function is set (through range_lookup argument) to return the value if it finds the exact match. But, by default (i.e. range_lookup argument is not supplied). Like the Lookup function, it returns the closest match below lookup value.