You can use worksheets as sources of data and then use one of the LOOKUP functions to retrieve data from it. For example, you might store the prices of computer equipment on one work sheet and records of sales on another work sheet. If you were dealing with computers you might use a separate work sheet for the case, motherboard, keyboard, mouse, hard disc, CD, sound card, etc.
This list was placed in a separate worksheet, which was then renamed (right click the tab and select 'Rename').
In the Sales worksheet the Item and Price are looked up from the data sheets, rather than being typed in each time one is sold. To insert a lookup item type '=' and then click on the relevant worksheet tab and on the cell required before pressing Enter to confirm.
This is a general-purpose lookup function, which can be used to look up data values from a single column or row. It is used typically to find a value which corresponds to another value.
For example: =LOOKUP("Chelsea",A2:A20,B2:B20)
This would search for 'Chelsea' in the range A2:A20 and return the value in the same row from the B column.
This function looks up values from a range on a worksheet and returns the value specified, typically from another column of the spreadsheet. It is used in spreadsheets where the data is arranged in columns, as in the examples seen here.
It searches for a value in the leftmost column of a table, and then returns a value in the same row from a column specified in the table.
This example uses the VLOOKUP function to locate values in a table of data items:
The use of VLOOKUP on this spreadsheet reduces the need for input by the user. The user looks up the code of an item that has been sold to a customer and enters it in the A column (ItemCode). The VLOOKUP function is used in the B column and the E column to look up the name of the item and its price in the table in the H, I and J columns:
=VLOOKUP(A3,$H$2:$J$10,2)
=VLOOKUP(A3,$H$2:$J$10,3)
The functions are almost identical except in the last parameter. The first parameter is the cell where the lookup item is found; the second parameter is the area of the spreadsheet where the item's values will be found (the lookup table); the third parameter is the number of the column in the lookup table where the required data item will be found. Thus for an item with code 1 the B column contains 'Coffee' and the E column contains '150'. Column K is explained here.
VLOOKUP can be used with ranked data to produce a separate sorted list. Given a set of data that includes, for example, a rank order, a name and a score these rows can be copied to another part of a worksheet with something like this:
=VLOOKUP($A30, $A$2:$C$24, 2, FALSE)
Note that this formula assumes that you have already entered the rank order of the sorted list (1, 2 ,3...) in cells A30 onwards. The first parameter is the value to be matched, the second parameter is the range where the values will be found, the third parameter is the column from which the data will be chosen while the fourth parameter specifies that an exact match of rank scores is required. The second parameter is given as absolute addresses so it can be copied without changing. The third parameter specifies a column number in the range A to C so the number 2 means column B. To add the scores to the sorted data a second VLOOKUP would have to be added in column C as follows:
=VLOOKUP($A30, $A$2:$C$24, 3, FALSE)
(identical apart from the third parameter, 3).
This function is similar to VLOOKUP but is used on tables where the headings run down a column and the data is in rows.
Use the Back Button to Return to the previous page