Finding a matching value in large set of data is a hectic task. Even if your data is arranged in a proper format or in a table, it’s quite hard to find a value in terms of specific data. When it comes to lookup the value inside a big table in Microsoft Excel, we use the functions called VLOOKUP and HLOOKUP. These reference functions are widely used in the large spreadsheets to get the value we actually wanted.
How Does VLOOKUP Work in Excel?
VLOOKUP is probably one of the most used Excel functions. We use VLOOKUP when we have a lookup value and we need to extract that same corresponding value in some other column. This function is mostly used in the situations when you are looking up for matching data in the leftmost (first) column. When that data is found then you go right hand side to get the exact value you wanted.
Let us take an example Excel table like below:
In this example, the percentage of marks are given for various students of your class and you need to find out what your percentage is. There are two ways to do this – either you can check by going through the ‘Roll No.’ Column or going to through the ‘Name’ column.
- First of all, you go to the list and start looking your name from top to bottom.
- Then as soon as you spot your name then you move your fingers and eyes towards the right to see your percentage of marks obtained.
And this is what VLOOKUP does for you within a fraction of seconds, the only thing you need is the syntax or formula to get your value right.
Syntax for VLOOKUP in Excel
Below is the syntax of VLOOKUP, ensure to start the formula with = sign:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])- lookup_value: This signifies the lookup value that you are trying to find out in the leftmost column of the spreadsheet or table.
- table_array: This is the whole data given in the table or entire table in which you are going to find out the value.
- col_index_num: It is a column index number in which you are going to fetch the matching value.
- range_lookup: This is the part where you want the approximate value (True or 1) or Exact match (False or 0).
How to Use VLOOKUP in Microsoft Excel?
There are many formats you can look up values in Excel, depending upon the size of the table. But here we are going to show you in the most effective and the widely used format. First of all, make a table in your Excel sheet. You can take any table, here we are making our own table in 3 columns (Product S.No, Product Name, status) as shown below:
In above table, we can see that ‘Product S.No.’ column is the column which has the value that we know. But let’s assume that in status column we don’t know about the “In stock” and “Out of stock” status of the product. So, we need to find whether the product is “in stock” or “out of stock” quickly. Let us make two columns for the result (Product S.No. & Status) in which we enter the Product S.No. and get to know whether it is “in stock” or “out of stock”.
Now, let us enter the ‘Product S.No.’ value ‘p001’ in the cell B10. We have to enter the formula for VLOOKUP which is =VLOOKUP(B10,A1:C6,3,FALSE) in the cell C10.
You can see the stock status for the product ‘p001’ (cell B10) is displayed as ‘In stock’ in the cell C10.
You can simply change the ‘Product S.No.’ to any of the values from first column in cell B10 to get the corresponding ‘Status’ from third column in cell C10. For example, entering ‘p005’ in cell B10 will show the result as ‘Out of Stock’ in cell C10. This is what VLOOKUP does and save lots of time when looking for corresponding values in larger Excel spreadsheets.
How Does HLOOKUP Work in Excel?
HLOOKUP function is not as widely used as the VLOOKUP function but it is quite similar to VLOOKUP. It is helpful when you are looking for a data in a horizontal row. When the data is found, you go down in that column and fetch the value from a cell. It will return the resultant value to you .
Syntax for HLOOKUP in Excel
Below is the syntax of HLOOKUP, ensure to start the formula with = sign:
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])- lookup_value: This signifies the lookup value that you are trying to find out in the leftmost column of the spreadsheet or table.
- table_array: This is the whole data given in the table or entire table in which you are going to find out the value.
- row_index_num: It is a row index number in which you are going to fetch the matching value.
- range_lookup: This is the part where you want the approximate value (True or 1) or Exact match (False or 0).
As you can see, the only difference between VLOOKUP and HLOOKUP is that VLOOKUP uses ‘col_index_num’ for fetching the matching value while HLOOKUP uses ‘row_index_num’ for getting the value.
How to Use HLOOKUP in Microsoft Excel?
Using HLOOKUP in Excel is as same as VLOOKUP, you just need to put the right formula in the cell and get the result right there. Let us learn this with a simple example.
Make a table in your Excel sheet, add some data to it. Our example table looks like below:
Now when you have your table, make a ‘Result’ cell. In this cell, you’ll see your result. Write a formula like =VLOOKUP(“HomePod”,B1:D3,2,FALSE) in the cell B6 under ‘Results’.
Here we are looking for the data in the second row but it is fetched through a column ‘HomePod’. Hence, we get $70 as a result. It’s same as VLOOKUP, the only difference is that we looking the data horizontally and getting our value.
Remember, when you provide the direct values like HomePad, you should enter the value inside double quotes. If you provide the cell reference as B1 then you can enter the cell number without quotes. We have used this cell reference in VLOOKUP example.
Conclusion
Finding a value with respect to a reference data is hard in larger Excel sheets. Microsoft Excel provides VLOOKUP and HLOOKUP functions to lookup the required values easily. In above guide, we have learned how to use VLOOKUP and HLOOKUP in Microsoft Excel. Make sure you are putting the formula carefully, otherwise your result may not be correct.
Leave a Reply
Your email is safe with us.