LOOKUP Function | Excel

Excel provides a number of logical functions which allow the user to define various "conditions" and have data result in response to them. When definite conditions of these functions are met, a pre-defined calculation will be performed or text will be displayed.

Lookup functions allow more than eight logical tests or scenarios. This gives advantage over If functions which can only nest up to seven (64 in later versions) IF clauses in a main IF statement. These functions allow the user to create formulae which examine large amounts of data to point information which matches or approximates to certain conditions. They are simpler to construct than nested IFs and can produce more variable results. The following paragraphes will explain four types of these functions called "Vector Lookup", "HLookup", "VLookup" and "Nested Lookup".

Vector Lookup

The Vector Lookup function or statement is used to look through a vector (series) of data that only occupies one row or column to find a specific value. When the value is found, a corresponding "result" in the adjacent row or column is returned. Typically, this function consists of three arguments (parts) enclosed in one set of parentheses and each is separated by a comma, as shown below:

=LOOKUP( lookup_value , lookup_vector , result_vector )

Where,

• ● LOOKUP_VALUE represents the number or text entry to look for.
• ● LOOKUP_VECTOR is the area in which to search for the LOOKUP_VALUE.
• ● RESULT_VECTOR is the adjacent row or column where the corresponding value or text is to be found.
• ☺ It is essential that data in the lookup vector to be placed in ascending order, numbers from lowest to highest or text from A to Z. Otherwise, the LOOKUP function may return a wrong result.

The formula can be inserted either by typing it or by selecting it from the Function Library on the formula’s ribbon, under heading of math & trig functions. The Function Library allow to view the above syntax (details) of LOOKUP function, using Mouse as follow:

• i. Click on the FORMULAS Ribbon.
• ii. Click the drop down arrow below the Lookup & Reference button and select LOOKUP from the displayed functions.
• 
  
  
  
• iii. A dialog box appears displaying the two versions of LOOKUP. There are two syntax forms; the first is the "VECTOR" and the second the "ARRAY".
• 
  
  
  
• iv. Choose vector and click OK. The next dialog box will appear and the three arguments can be seen within the box.
• 
  
  
  
• v. Enter the values as described previously and click OK.

For next example, column D of a spreadsheet may contain figures, and the adjacent column E contains corresponding text. LOOKUP will search for the requested figure in column D and return the corresponding text from column E.

In the next diagram, column D contains varying salaries, against which there is a company car in column E corresponding to each salary.

For example, a £20,000 salary gets a GOLF, a £35,000 salary gets a SCORPIO. A LOOKUP formula can be used to point which car is appropriate to a salary figure that is specified. 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 equals;

=LOOKUP(B13,D3:D11,E3:E11)

Entering £40,000 in cell B13 will define 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 is MERCEDES in this case.

Alternatively, the formula could be simplified without cell references by defining appropriate Names on cell and both ranges. Define B13 as Salary, D3:D11 as Salaries and E3:E11 as 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 smaller than the exact value. For example, if a user inputs a Salary of 22000 which doesn't match any figure in the given Salaries range, the nearest given salary below 22000 is 20000 for a GOLF. This technique is very useful when the LOOKUP_VECTOR indicates grades or bounds. In other words, everyone in the salary bound between 20000 and 25000 could get a GOLF. Meanwhile, only those with salary which meets or exceeds 25000 could get a SIERRA. Whenever, the LOOKUP_VALUE is smaller than the lowest value in the vector, the #N/A error message is displayed.

Hlookup

The horizontal LOOKUP function (HLOOKUP) can be used on either a "VECTOR" (single column or row of data) or an "array" (multiple rows and columns). HLOOKUP searches for a specified value horizontally along the top row of an array. When the value is found, HLOOKUP searches down to a specified row and displays the value of the cell in the same column. This is useful when data is arranged in a large tabular format, and it would be difficult for the user to read across columns and then down to the appropriate cell. HLOOKUP will do this automatically.

The syntax for HLOOKUP is;

=HLOOKUP( lookup_value , table_array , row_index_number)

Where,

• ● LOOKUP_VALUE is, as before, a number, text string or cell reference which is the value to be found along the top row of the data.
• ● TABLE_ARRAY is the cell references (or range name) of the entire table of data.
• ● ROW_INDEX_NUMBER represents the row from which the result is required. This must be a number, e.g. 4 instructs HLOOKUP to extract a value from row 4 of the TABLE_ARRAY.
• ☺ It is important to remember that data in the array must be in ascending order during HLOOKUP search process, horizontally across the array. Where, HLOOKUP uses an array with more than one column or row of data. Same as LOOKUP, if this rule is ignored, HLOOKUP will return the wrong value.

As shown in next example, a spreadsheet displays different rates of interest for a range of amounts over different time periods;

Whatever a customer wants to borrow an amount, 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 point to the desired amount, then move down the array to point to the corresponding interest rate for the required time period.

Considering cell A51 as the cell to hold the amount & the LOOKUP_VALUE, cells C43:H48 are the TABLE_ARRAY and 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 HLOOKUP is that it does not necessarily have to find the exact LOOKUP_VALUE. For example, if user wants 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% will appear. As before, Excel has looked for the value in the array closest to, but smaller than, the LOOKUP_VALUE.

Vlookup

The VLOOKUP function works on the same principle as HLOOKUP, but instead of searching horizontally, VLOOKUP searches vertically. VLOOKUP searches for a specified value vertically down the first column of an array. When the value is found, VLOOKUP moves across to a specified column and points to value of the cell.

The syntax for the VLOOKUP function follows the same statement 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 )

Where,

• ☺ 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, a VLOOKUP formula could be used to search for a specific time period, then return the appropriate rate for a fixed amount.



A time period is entered in cell A54 and the next VLOOKUP formula is entered in B54;

=VLOOKUP(A54,C43:H48,5)

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. Therefrom, a £40000 loan over 17 years would return an interest rate of 16.00%.

Nested Lookups

One of the limitations of the horizontal and vertical LOOKUP functions is that for every LOOKUP_VALUE changed, the column or row index number stays constant.

Using the same example, the HLOOKUP will search for any amount, but always for the same time period. Conversely, the VLOOKUP will search for any time period, but always for the same amount. In both cases, if you want to change the time period and the amount the formula must be edited to change the column or row index number.

There is, however, a technique whereby one LOOKUP function is "nested" within another. This looks up one value, which will then be used in a second LOOKUP formula as a column or row index number. Using this technique allows the user to enter a time period and an amount and check the correct interest rate.

Because nested LOOKUPS have more than one LOOKUP_VALUE, more than one array is needed. This second array should consist of the column or row numbers to be used in the LOOKUP formula. Below spreadsheet shows main interest rates, with two additional columns of data;

Column J contains all the same time periods as column C, but alongside this are numbers 2 to 6, indicating the ROW_INDEX_NUMBER to be returned for the appropriate time period. To look up this value, use the following simple vector LOOKUP formula;

=LOOKUP(K50,J43:J47,K43:K47)

Where, K50 is the required time period, J43:J47 is the LOOKUP_VECTOR and K43:K47 is the RESULT_VECTOR.

Notice there is no equals sign, because this formula is not being entered in a cell of its own. The formula will return a value between 2 and 6 which will be used as a ROW_INDEX_NUMBER in further formula of HLOOKUP. This HLOOKUP will look in the main interest rate table for a pre-defined amount, and will respond to the ROW_INDEX_NUMBER returned from the nested LOOKUP formula. The cells J50 and K50 hold the amount and time period to be entered by the user, and the entire nested HLOOKUP, typed in J52, is as follows;

=HLOOKUP(J50,C43:H48,LOOKUP(K50,J43:J47,K43:K47))

In this example, the time period 25 is vertically looked up in COLUMN J and the corresponding value 5 is returned. Also, the amount 40000 is horizontally looked up in the main table, with a ROW_INDEX_NUMBER of 5. The end result is pointed equal to interest rate of 18.50%. Simply by changing cells J50 and K50, the correct interest rate is always returned for the typed in amount and period.