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

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
  1. Access the VLOOKUP file downloaded from the above link.
  2. Click on cell C27 and type G4225.
  3. Click on cell C29 and enter the function to display the SALE PRICE given the value in C27 =VLOOKUP(C27, A4:H21, 2, FALSE).
  4. Use of the function to display SUPPLIER, SPECIFIC, SALE PRICE and DESCRIPTION.
    P0748

Test the result

  1. Click on cell C27 again and type another code such as G7653.
  2. Check that all data displayed is accurately transferred from the table.
  3. Try entering a code, say B05, and check the results. You should get an error message.
  4. 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_valueCell where code is entered
able_arrayThe table of data A4:R12
Col_indexSale Price row which is 5
Range_lookupFALSE so an exact match is found
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.