V & H LOOKUP FUNCTION

These functions provide a means of reading individual data values from lookup tables (also known as arrays or ranges). Excel allows you to perform both vertical and horizontal lookups by using VLOOKUP and HLOOKUP functions.

Vertical lookup
Vertical lookup Searches the leftmost column of a lookup table for a specific “Search Value” and then returns a value contain in the intersecting cell of the relevant row and relevant column of the lookup table.
Note: Function abbreviation of Vertical lookup is “VLOOKUP”

Vlookup Syntax
VLOOKUP (lookup value, table array, column index number, Type)
Lookup Value – is the search value (i.e. the item being search for in the leftmost column of the table). This search value may either be a number, a text entry (which needs to be enclosed in double quotation marks), or a cell or range name.

Table Array – is the range reference or range name for the table containing the information (ie. Lookup Table).

Column Index num –is the column number in the table containing the information that is to be returned.

Type – is an optional argument for exact match or approximate match. • If True – Lookup function searches the left most column of the lookup table for the largest number less than or equal to the search value.

Note that if you do not enter the type, Lookup function takes True type as default type.

• If False - Lookup function searches for an exact match.

Important – Before you can perform the Lookup function it is important to sort the table array according to the values in the left most column in ascending order. Important – Ensure leftmost column in lookup table contains unique values.

Hint: whenever VLookup function uses a text entry (eg. “Monday”) as its search value, its type argument must be set to False, but the left most column of the table does not need to be sorted into ascending order.

Activity 1
How to write a formula to calculate tax rate for an income level
Income Level Tax Rate
Rs.0.00 – Rs.9.999.00 15%
Rs.10,000.00 – Rs.29,999.00 30%
Rs.30,000.00 – Rs.99,999.00 34%
Rs.100,000.00 and over 40%

Activity 2
Given a product ID, how can I lookup the product’s price
Product ID Price Rs.
A134 3.50
B234 4.20
F167 5.60
X567 5.90
R356 5.70
S123 4.20

Activity 3
Sales discounts should be calculated based on the sales amount and the selected discount plan. Four discount plans together with relevant sales ranges are given below.
|Sales |Plan 1 |Plan 2 |Plan 3 |Plan 4 |
|0 |0.0% |0.0% |0.0% |0.0% |
|20,000 |2.0% |3.0% |4.0% |4.8% |
|40,000 |2.1% |3.1% |4.2% |4.9% |
|60,000 |2.2% |3.2% |4.3% |5.0% |
|80,000 |2.3% |3.3% |4.4% |5.1% |
|100,000 |2.4% |3.4% |4.5% |5.2% |

Develop a model to calculate reverent discount amounts for given sales value and discount plan.

Activity 4
Use Vlookup function for the product repair decision example in the previous lesson.

Horizontal Lookup
HLookup is identical to VLOOKUP function, except it searches across the first row of the lookup table (rather than down its first column) for a given value.
Syntax
HLOOKUP (lookup value, table array, row index number, type)
You will notice that the only deference in the syntax of the HLOOKUP function compared to the VLOOKUP function is that it contains a row index number rather than a column index number. Other things are same as the application of VLOOKUP function.

Activity 5
Suppose that a product’s price changes over time. Following details shows the price charged in different time periods. I know the date the product was sold. How can I write a formula to see the product price charged in that period?
Date sold Price
January – April 2005 Rs.98
May – August 2005 Rs.105
September – December 2005 Rs.112
January -2006 onwards Rs.120

Example 1
The file “Hr data” gives employee ID codes, Salaries and years of experience. Write a formula that takes a given ID code and yields the employee salary. Write another formula that takes a given ID and yield the employee’s years of experience.

Example 2
You are thinking of advertising your products on a sports telecast. As you buy more ads, the price of each ad decreases as shown in the table.
Number of ads Price per ad
1-5 Rs.12,000
6-10 Rs.11,000
11-20 Rs.10,000
More than 20 Rs.9,000

Example 3
Using the same data table now write a formula to calculate the advertising cost as given below.
If you by 8 ads then first 5 you need to pay 12000 per add and next 3 pay 11000 and so on

Example 4
Develop the database of your employees by using necessary information required to develop pay slip.
Develop pay slip for the employees of your company by entering employee’s number and extract the following information’s.
Name of the employee
Grade
Basic salary
Advances
Loans
OT payments
Advance deductions
Loan deductions
EPF deductions of 10%
No pay dedications
Net salary
Company contribution to EPF of 15%
Company contribution of ETE of 3%