7.9 Lookup functions
The VLOOKUP function
The VLOOKUP function is used when your data is arranged in columns. It enables you to select a piece of data based on a specific value.
![P0746](https://www.cambridge.edu.au/go/epub/library/pit2/OEBPS/images/module07/P0746.png)
The VLOOKUP function could be used with the data above to display information about a specific product from the product code. For example, if you were asked to look up the sale price of product code G5313, you would scan down the CODE column until you found the code and then scan across the row to the SALE PRICE column to find that data. The VLOOKUP function works in the same way. The function must be written in a specific format so that it works correctly:
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
Lookup_value | This is the cell reference where the code for the item you are interested in is initially entered. |
Table_array | This is the cell range of the table. In the table above, it is A4:H21. |
Col_index | This is the number of the column where the specific data will be found, so if you require Sale Price, then 5 will be entered here. If you require the Supplier, 3 will entered, and so on. |
Range_lookup | This is used to determine if you require an exact match for the code. Use False if you do. |
Spreadsheets exercise 15
The VLOOKUP function
Skills practised
- Open a file
- Entering a formula
- Use the VLOOKUP function
- Test formula
- Save file
- Access the VLOOKUP file downloaded from the above link.
- Click on cell C27 and type
G4225
. - Click on cell C29 and enter the function to display the
SALE PRICE
given the value in C27=VLOOKUP(C27, A4:H21, 2, FALSE)
. - Use of the function to display
SUPPLIER, SPECIFIC, SALE PRICE
andDESCRIPTION
.
Test the result
- Click on cell C27 again and type another code such as
G7653
. - Check that all data displayed is accurately transferred from the table.
- Try entering a code, say
B05
, and check the results. You should get an error message. - Save and close the VLOOKUP file.
The HLOOKUP function
The HLOOKUP function works in a similar way to the VLOOKUP function, but where the VLOOKUP function scans vertically down a column, the HLOOKUP function scans horizontally across a row. Of course, the data must be set up in an appropriate manner. The function must be written in a specific format so that it works correctly:
HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)
Lookup_value | Cell where code is entered |
able_array | The table of data A4:R12 |
Col_index | Sale Price row which is 5 |
Range_lookup | FALSE so an exact match is found |
![A0712a.png](https://www.cambridge.edu.au/go/epub/library/pit2/OEBPS/images/module07/A0712a.png)
The HLOOKUP function could be used with the data above to display information about a specific product from the product code. For example, if you were asked to look up the sale price of product code G4230, you would scan across the Code row until you found the code, then scan down the column to the Sale Price row to find that data – $6,998 in this case. The HLOOKUP function works in the same way.
Skills practised
- Open a file
- Entering a formula
- Use the HLOOKUP function
- Test formula
- Save file
The HLOOKUP function
- Access the HLOOKUP file from the above download link.
- Click on cell C15 and type G5313.
- Click on cell C17 and enter the function to display the NAME of the item given the value in C15.
=HLOOKUP(C15, A4:R12, 2, FALSE)
- Use the function to display SPECIFICS, SALE PRICE, NO ON HAND and DESCRIPTION.
Test the result
- Click on cell C15 again and type another code, such as G7653.
- Check that all data displayed is accurately transferred from the table.
- Try entering a code, say GCC, and check the results. You should get an error message.
- Save and close the HLOOKUP file.