LOOKUP Function
The lookup function allows the user to set up a look up cell. When a cell address of a cell in the lookup range is input into this cell, the contents of that cell will be returned to the cell containing the lookup function.
Syntax
LOOKUP(lookup cell,cell number..cell number)
Lookup cell is the cell where the lookup location is input.
Cell number...cell number is the range of cells included in the lookup. This range can be from a row, or a column, or from an array.
Example 1
Where cell A2 contains the function LOOKUP(a1,c1..c10)
And where the other cells look like this:
A1 contains 2 (formatted as an input cell)
C1 contains 100
C2 contains 200
C3 contains 400
C4 contains 600
C5 contains 800
C10 contains 1200
The LOOKUP function returns the number 200, the contents of the cell C2.
The function returns the value of any cell number between C1 and c10 when its cell address is input into the cell A1. Formatting the cell with the look up function (cell A2) as an alphanumeric cell allows both numeric and text contents to be returned. If the cell referenced is an alphanumeric cell, the function automatically converts the value to a number.
If the cell is not within the range specified, the function returns 0.
Example 2
Where table T1 contains two columns and four rows of cells labelled T1.A1 - T1.A4 and T1.B1 - T1.B4.
Column A contains account number 101, 102, 103, and 104, and column B contains the applicable current year balance.
Cell C1 is an input cell where a user can enter an account number in form mode, such as 103.
LOOKUP(C1,T1.A1..T1.B4) will find the account number 103 and return its corresponding balance from column B.
Notes
- This form of a complex LOOKUP formula must search for a specific value within the first column's range rather than a relative location such as "2" in the first example.
- The columns to be searched must be adjacent for the function to return the correct value.
