Have you wondered what the VLOOKUP and HLOOKUP functions in Excel do ?
VLOOKUP is simply a function that looks up a column in your spreadsheet and gets the corresponding columns of that row.
For Eg: say you have a spreadheet like the one given below :
There are 7 rows of information.
To get the product price or the product description or the product discount from product code is easy. Simply use the VLOOKUP function.
Here is the format of the VLOOKUP function.
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
The VLOOKUP function syntax has the following arguments:
- lookup_value Required. The value to search in the first column of the table or range. Thelookup_valueargument can be a value or a reference. If the value you supply for the lookup_value argument is smaller than the smallest value in the first column of thetable_array argument, VLOOKUP returns the #N/A error value.
- table_array Required. The range of cells that contains the data. You can use a reference to a range (for example, A2:D8), or a range name. The values in the first column of table_arrayare the values searched bylookup_value. These values can be text, numbers, or logical values. Uppercase and lowercase text are equivalent.
- col_index_num Required. The column number in the table_array argument from which the matching value must be returned. A col_index_num argument of 1 returns the value in the first column in table_array; acol_index_num of 2 returns the value in the second column intable_array, and so on.
Now assume that we want to look up the product price for a pendrive (the product code is 102).
Here’s how to do it.
You see the VLOOKUP function passes all the four parameters as shown above and we get product price as the result.
Our VLOOKUP function is VLOOKUP(102,A3:D9,3,FALSE)
the first parameter 102 is the lookup code (product code in our case) and the second parameter is the range (A3:D9) which is where the data matrix is, the third parameter is the lookup value indec, in this case we need the product price hence it is 3rd column or 3. The last parameter states that an exact value is required (FALSE), If you make it TRUE, the most nearest value will be extracted.
Now to get the discounted price we have to subtract the discount (4th Column) from the original price. This is illustrated below:
If you have horizontally partitioned your data like this:
In this case you can use the HLOOKUP Function, It’s similar to VLOOKUP, except it works on columns instead of rows.
So here’s the first HLOOKUP
Note that the result is the same as VLOOKUP. Here the only change is the data matrix range.
For a discounted price we do the same as VLOOKUP. Please see below:
Both VLOOKUP and HLOOKUP are powerful lookup functions that you can use in Excel.