Hlookup and vlookup to calculate salary

In the previous tutorial has been discussed on how to use hlookup and vlookup in a data. At this time will be discussed further use hlookup and vlookup simultaneously on a data.

There are 4 points that will be discussed in this tutorial

1. Calculate the base salary (vlookup)

2. Calculate allowance (vlookup)

3. Calculating transport (vlookup)

4. Calculate taxes (hlookup)

The writing of HLOOKUP function =HLOOKUP(lookup_value,tabel_array,row_index_num, [range lookup])

The writing of VLOOKUP function =VLOOKUP(lookup_value,tabel_array,col_index_num, [range lookup])

Open your excel file and create a table like the example below.

Picture 01. Salary table

Start calculating the data

1) Calculate the base salary

Using vlookup function. Make vlookup function in cell D7

=VLOOKUP(C7,B15:E19,2,FALSE) change to absolute by pressing F4

=VLOOKUP(C7,$B$15:$E$19,2,FALSE)

Picture 02. Calculate base salary

Test the function to see results. Then copy and paste or drag function into cell D11.

2) Calculate allowence

Using vlookup function. Make vlookup function in cell E7.

=VLOOKUP(C7,$B$15:$E$19,3,FALSE)

Picture 03. Calculate allowance

Test the function to see results. Then copy and paste or drag function into cell E11.

3) Calculate transport

Using vlookup function. Make vlookup function in cell F7.

=VLOOKUP(C7,$B$15:$E$19,4,FALSE)

Picture 04. Calculate Transport

Test the function to see results. Then copy and paste or drag function into cell F11.

4) Calculated tax

Using HLOOKUP function. Make vlookup function in cell H7.

=HLOOKUP(C7,$G$15:$I$16,2,FALSE)

Picture 05. Calculate Tax

Test the function to see results. Then copy and paste or drag function into cell H11.

To calculate the total salary as usual using the formula = SUM (D7: F7) in cell G7 into cell G11

To calculate net salary, you can use a formula like this = G7- (G7 * H7) in cell I7 into cell I11.

degineering