VLOOKUP & HLOOKUP

These two functions are used to reference one table with another.

For example in one sheet called DATA we have:

A B C
1 Grade A B
2 Commission 5% 7%

This would need an HLOOKUP because the info is Horizontal.

On the other sheet called SALES we have:

A B C
1 Grade Surname Firstname
2 A McGowan M
3 A Alexander R
4 B Shute G


The formula =HLOOKUP(A2, Data!$A$1:$C$2,2)

This would return the value of 5%.

Looking at the above formula in detail:

=HLOOKUP means that is the function you need
A2 is the cell which is the reference in the sheet you are going to enter the new data
Data! is the name of the other sheet
$A$1:$C$2 is the range of the table (in this case a Horizontal table)
,2 means we want the values in the second row to be returned.

The same is true for VLOOKUP. The only difference is the table is arranged in Vertical format.