Using Lookup Functions in Microsoft Excel
Written by Ian Fraser
VLookup and HLookup Functions
=VLookup(Input cell,Table Range,Column count)
=HLookup(Input cell,Table Range,Row count)
Excel provides two functions whose purpose is to extract (look up) data from a table and use it elsewhere.
VLOOKUP and HLOOKUP are nearly identical functions that look up information stored in tables that you have constructed. The more commonly used of these functions is VLOOKUP, or “Vertical Lookup”. It searches a designated area of a worksheet for specific information, and it does it column by column.
The less commonly used function is HLOOKUP or “Horizontal Lookup”. It first looks for a column heading, then goes to a specific row and retrieves the information in that cell.
When constructing tables for VLOOKUP or HLOOKUP functions it is good practice to arrange the values in the first column (VLOOKUP tables) or the first row (HLOOKUP tables) in ascending order to ensure that you receive the correct results.
If your Lookup table has not been sorted into logical order you may not the receive the correct results. You can fix this by typing a comma ( , ) and adding the word FALSE or a 0 to your formula. =VLookup(B5,A15;B21,2,FALSE) or =VLookup(B5,A15;B21,2,0)
Example 1
=VLookup(A2:B2:C11,2) – returns 7%
Example 2
=HLookup(B1,B3:K4,2) – returns 5%
Example 3
=VLookup(A2,A6:C9,2) – returns Ian
=VLookup(A2,A6:C9,3) – returns $25,000
Example 4
=VLookup(A2,A6:C9,2) – returns #N/A as the employee number does not exist
=VLookup(A2,A6:C9,3) – returns #N/A as the employee number does not exist
|
