How to use the VLOOKUP and HLOOKUP formulas on Ms. Excel

Questions about how to use the Vlookup and Hlookup formulas are often heard among job seekers. Why? because most companies provide this requirement as a standard qualification that is usually placed in the administration.

Then do you know about Vlookup and Hlookup? Vlookup is used to look up values in vertical form, according to the prefix V which stands for Vertical / Perpendicular. Whereas Hlookup starting with the Letter H is used to look up values in line / horizontal form.

Then how to use it? below we will review how to use the Vlookup and Hlookup formulas on Ms. Excel.

How to use the VLOOKUP and HLOOKUP formulas on Ms. Excel

Example Problem VLOOKUP and HLOOKUP: Open ms. office excel then type the questions below:

Looking at the sample questions above, all you have to fill in is to calculate the basic salary, allowances, transportation, total salary, taxes, net salary and total.

1. Calculating the basic salary
To calculate the base salary use the vlookup function in cell D5. After filling the formula in cell D5, copy the formula or drag it to cell D9. The form of writing the function is the same as previously described, namely, = VLOOKUP (lookup_value, table_array, col_index_num).

All you need to know is lookup_value, filled with the value as the base (key value) of reading the data table. Table_array is a range of data arranged vertically or vertically which functions as a auxiliary table to be read. Col_index_num, column serial number for table reading starting from the leftmost column.

Note: because the position of the “salary table” to be read is fixed, to avoid errors when copying the formula or drag adding $, to isolate the cell / range. You can see the process of the process as shown below:

2. Calculating Allowances
To calculate the allowance, use the VLOOKUP function in cell E5. After filling in the formula in cell E5, copy the formula or drag it to cell E9. As shown below:

3. Calculate transportation
Use the VLOOKUP function in cell F5. After filling in the formula in cell F5, copy the formula or drag it to cell F9. See the image below, for the results:

4. Calculate the total salary
To calculate the total salary, use the SUM function in cell G5, write the formula = SUM (D5: F5). After filling the formula in cell G5, copy the formula or drag it to cell G9. Or you can also add it directly in cell G5 to be like this: = D5 + E5 + F5, then copy down.

5. Calculating Tax
To calculate tax, use the HLOOKUP function in cell H5, copy the formula or drag it to cell H9. Like the picture below.

6. Calculating Net Salary
Use the SUM function in cell i5 like this = sum (G5-H5), copy the formula or drag it to cell I9. Or you can also use the command = G5-H5, then copy or drag down to cell I9.

The last command is to calculate the “total” of each part that you worked on earlier, using the SUM function in cell D10, copy the formula or drag it to the right to I10. The final result of all the work above, can be seen as shown below:

Leave a Reply

Your email address will not be published. Required fields are marked *