How to learn vlookup and hlookup in MS Excel sheet?

In excel spreadsheet software, there are many functions and vlookup and hlookup are two of them. It is actually to look up for certain data and if it is seen then do some action. The letter v in vlookup means vertical that is column related and the letter h in hlookup means horizontal that is row related. The function vlookup is generally used where headers are column wise whilen hlookup is used when headers are row wise.

The general syntax of a vlookup function is like -
vlookup (lookup_value, range of cells or table array, column number to be searched, false)
The parameter false is necessary to be included to avoid some closest matches (not exact) to be reported. The requirement for this function vlookup to work is that the entity should be written in leftmost column and the value should be in the right side.

For example let us take some items (Column A) and their inventory (Column B) available -
Column A ________ Column B
Item1____________ 200
Item2 ____________30
Item3 ____________50
Item4 ____________130

If I want to find out inventory of item2 to be given in cell number B6 then I would go to cell A6 and go to function bar and write the syntax vlookup (A6, A1:B4, 2, false) and I would get the name of item written in A6 and inventory in B6. This is a very small example but once the basic is clear one can use it for a big spreadsheet.

Similar way one can write hlookup function. Instead of columns treat the rows there in same way.

You can learn all stuff related to MS. Office from youtube and other online tutorials. You can sign up at coursera too.

VLOOKUP function of Microsoft Excel is a very useful utility to lookup for data or find data corresponding to a particular value. Excel is a very powerful set of tools used for large data analysis, compilation, and summarization. Sometimes in this huge chunk of data, we lose some corresponding value or particular value attached to a particular field, here Vlookup function comes in handy.

Vlookup if simply means Vertical lookup and HLOOKUP means to look Horizontally. When you use Vlookup you look for data in columns and when you use HLOOKUP you look for data in rows.

When you put the formula in an excel cell you get something like this: VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

This can be put in as -
lookup_value - What you want to lookup
table_array - where you want to look for it
col_index_num - the column number in the range containing the value to return
range_lookup - return an Approximate or Exact match – indicated as 1 or 0, where 1 is TRUE and 0 is FALSE.

Now, always put the value column right next to the column from which you are trying to extract data, this will help in easy search of data and you will always put 2 as the col_index_num.

Now even simple and more powerful function is introduced by Microsoft Excel i.e XLOOKUP and this will make VLOOKUP and HLOOKUP functions redundant. This formula only needs 3 values to find the data you need and there is no need for looking for it horizontally or vertically.

1. VLOOKUP function: The VLOOKUP function is called the 'Vertical Lookup' as it executes its function for our search or certain value in a Column( Top to Bottom or visa versa). It is an inbuilt function of MS Excel that can be executed in the column by using a formula in a cell of a worksheet. This function(VLOOKUP) is available in all the version of MS office above 2000 i.e. Excel 2000, XP, Excel 2003, Excel 2007, Excel 2010, Excel 2011(MAC), Excel 2013, Excel 2016, Excel 2019 and Office 365.

The Syntax sued in VLOOKUP is : =VLOOKUP([value], [range], [column number], [false or true]), where
1. Value = It is the value you are looking for in the first column of the sheet/table.
2. Range(or table array) = It is the range/ table array in which we want to find the value and the return value.
3. Column Number(index_number) = It is the column number/index_number within your defined range, that contains the return value.
4. Match (True or False) = It is optional. You can either omit or complete your formula with this. In the formula "0 or FALSE" is used to get an exact match for the value we are looking for; or "1 or TRUE" for an approximate match. If you omit this parameter, it will show the default parameter which is True.

Ex: Find in the attachment for VLookup

2. HLOOKUP function: The HLOOKUP function is called the 'Horizontal Lookup' as it executes its function for our search or certain value in a Row( Left to Right or visa versa). It is an inbuilt function of MS Excel that can be executed in the row by using a formula in a cell of a worksheet.

The Syntax sued in HLOOKUP is : =HLOOKUP([value], [range], [row number], [false or true]), where
1. Value = It is the value you are looking for in the first row of the sheet/table.
2. Range(or table array) = It is the range/ table array in which we want to find the value and the return value.
3. Row Number(index_number) = It is the Row number/index_number within your defined range, that contains the return value. Your 1st row that contains a value is your taken as 1, the next Row as 2 and so on.
4. Match (True or False) = It is optional. You can either omit or complete your formula with this. In the formula "0 or FALSE" is used to get an exact match for the value we are looking for; or "1 or TRUE" for an approximate match. If you omit this parameter, it will show the default parameter which is True.

Ex. In the same example in the attached sheet if you have your data in horizontal format, you can use HLOOK UP formula in the same way but select your range accordingly.

In order to find the specific values either for the rows or columns, we need to go through the spread sheet provided in MSExcel.
In case for searching the horizontal values, we would apply Hlookup where as for the vertical values Vlookup is to be referred to. Although the difference between Vlookup and Hlookup are minor , but still the difference would appear significant for the learning curves in the Excel Shed.
One has to assign some specific value inside the cell so as to get the desired results. Here are some remarkable things with which one should should be familiar while applying Vlookup functions -
1) To make Vlookup effective in course of use, absolute reference range is to be applied so that one can have the exact values always.
2) With Vlookup, one can search both the approximate and exact match query.
3) By default, Vlookup to work, one should look at the values available in the first column of table array.
4) Vlookup is used for the table in an organised way in the vertical rows so that each row has some specific value in the cell.
5) In order to get effective result from the match mode, the datas are to be arranged in the ascending order.
6) Vlookup functions are case in sensitive. So inspite of different confugarations of Apple such as APPLE, ApplE etc, it would search Apple only.
Defining Hlookup -
Hookup stands for the Horizontal Search defining a set of look up function used for locating values from the specific row in the table. For this, one should carry out the following tasks -
1) Look up Values of Hlookup function are determined by matching the first row of the table.
2) Range _look up defines the value for an exact match in the Hlookup function.
3) In the exact match set, the range _ looks up to be false.