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. Theargument can be a value or a reference. If the value you supply for the l*lookup_value*argument is smaller than the smallest value in the first column of the*ookup_value*argument,*table_array***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 ofare the values searched by*table_array*. These values can be text, numbers, or logical values. Uppercase and lowercase text are equivalent.*lookup_value***col_index_num**Required. The column number in theargument from which the matching value must be returned. A*table_array*argument of 1 returns the value in the first column in*col_index_num*; a*table_array*of 2 returns the value in the second column in*col_index_num*, and so on.*table_array*

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.