Lookup!!

LOOKUP

As the name suggests, Excel gives us the option to lookup for a number or text in a specific area which needs to be stated. If the value is found the corresponding value or text is returned

The syntax for LOOKUP is as follows;
=LOOKUP( lookup_value , lookup_vector , result_vector )
 

 

 

In the diagram, column D contains varying salaries, against which there is a company car in column E which
corresponds to each salary. For example, a £20030 salary gets a Golf, a £35000 salary gets a Scorpio. A
LOOKUP formula can be used to return whatever car is appropriate to a salary figure that is entered. In this
case, the lookup_value is the cell where the salary is entered (B13), the lookup_vector is the salary column
(D3:D11), and the result_vector is the car column (E3:E11). Hence the formula;
=LOOKUP(B13,D3:D11,E3:E11)

Typing 40000 in cell B13 will set the lookup_value. LOOKUP will search through the lookup_vector to find
the matching salary, and return the appropriate car from the result_vector, which in this case is Mercedes.
Alternatively, the formula could be simplified and cell references avoided by using Formula, Define Name to
give appropriate range names. Call B13 Salary, D3:D11 Salaries and E3:E11 Cars. The LOOKUP formula could then be simplified to;
=LOOKUP(Salary,Salaries,Cars)

One of the advantages of the LOOKUP function is that if the exact lookup_value is not found, it will
approximate to the nearest figure below the requested value. For instance, if a user enters a Salary of
23000, there is no figure in the Salaries range which matches this. However, the nearest salary below 23000
is 20030, so the corresponding car is returned, which is a Golf. This technique is very useful when the
lookup_vector indicates grades or "bands.” In this case, anyone in the salary "band" between 20030 and
25000 gets a Golf. Only when their salary meets or exceeds 25000 do they get a Sierra.
There may be occasions where the lookup_value is below the lowest
value in the vector. In this case the #N/A message is displayed.

It is essential that data in the lookup vector is placed in ascending order, i.e. numbers from lowest
to highest, text from A to Z. If this is not done, the LOOKUP function may return the wrong result.

HLOOKUP
The horizontal LOOKUP function (HLOOKUP) can be used not just on a "vector" (single column or row of data), but on an "array" (multiple rows and columns).

The syntax for HLOOKUP is;
=HLOOKUP( lookup_value , table_array , row_index_number)

As HLOOKUP searches horizontally (i.e. across the array), data in the first row must be in ascending order, i.e. numbers from lowest to highest, text from A to Z. As with LOOKUP, if this rule is ignored, HLOOKUP will return the wrong value. Whatever the amount a customer wants to borrow, he may pay up to five different rates of interest depending on whether the loan is over 10, 15 or more years. The HLOOKUP function will find a specific amount, then move down the array to find the appropriate interest rate for the required time period. Designate cell A51 as the cell to hold the amount, i.e. the lookup_value; cells C43:H48 are the table_array; the row_index_number will be 2 if a customer wants the loan over 10 years, 3 if he wants the loan over 15 years, and so on. Cell B51 holds this formula;
=HLOOKUP(A51,C43:H48,3)

The above formula looks along the top row of the array for the value in cell A51 (30000). It then moves
down to row 3 and returns the value 15.00%, which is the correct interest rate for a £30000 loan over 15
years. (Range names could be used here to simplify the formula).
As with the LOOKUP function, the advantage of HLOOOKUP is that it does not necessarily have to find the
exact lookup_value. If, for example, you wanted to find out what interest rate is applicable to a £28000
loan, the figure 28000 can be entered in the lookup_value cell (A51) and the rate 14.30% appears. As
before, Excel has looked for the value in the array closest to, but lower than, the lookup_value.

VLOOKUP
The VLOOKUP function works on the same principle as HLOOKUP, but instead of searching horizontally,
VLOOKUP searches vertically.

The syntax for the VLOOKUP function follows the same pattern as HLOOKUP, except that instead of
specifying a row index number, you would specify a column index number to instruct VLOOKUP to move
across to a specific column in the array where the required value is to be found.
=VLOOKUP( lookup_value , table_array , col_index_number )

In the case of VLOOKUP, data in the first column of the array should be in ascending order, as VLOOKUP
searches down this column for the lookup_value. In the same spreadsheet as before, a VLOOKUP formula could be used to search for a specific time period, then return the appropriate rate for a fixed amount. In the following example, a time period is entered in cell A54 and in B54 the VLOOKUP formula is contained;
 

The cell A54 is the lookup_value (time period), the table_array is as before, and for this example rates are
looked up for a loan of £40000, hence the column_index_number 5. By changing the value of cell A54, the
appropriate rate for that time period is returned. Where the specific lookup_value is not found, VLOOKUP
works in the same way as HLOOKUP. In other words, the nearest value in the array that is less than the
lookup_value will be returned. So, a £40000 loan over 17 years would return an interest rate of 16.00%.