VLOOKUP(lookup value, table, column offset, [logical])

VLOOKUP is used to search for a value in the left hand column of a range, then return the value in the same row from the column you specify. There is an optional fourth parameter that specifies whether the value looked up should be an exact match (FALSE) or an approximate match (TRUE), which is also the default.

The example table shows the grade and published results students will get for the marks they achieve in an exam.

Input a mark in cell B2 (coloured light blue) and see how the VLOOKUP formulas in rows three and four change.

Example1
Grade: =VLOOKUP(B2,B6:D14,2). The nearest match, below the input number, is selected.
Result: =VLOOKUP(B2,B6:D14,3). Again, the nearest match below the input number is selected.

Example2
Grade: =VLOOKUP(B2,tbResults,2).
Result: =VLOOKUP(B2,tbResults,3).
The same as in Example1 but the table range has been given a name

Example3
Grade: =VLOOKUP(B2,tbResults,2,FALSE)
Result: =VLOOKUP(B2,tbResults,3,FALSE)
Uses the optional fourth argument, FALSE, to specify that an exact match is required. Change the mark, in cell B2, to any of those shown in the Mark column and these formulas will work.

   Missing: Microsoft Office Web Components
 
This page requires the Microsoft Office Web Components.

Click here to install Microsoft Office Web Components..

This page also requires Microsoft Internet Explorer 4.01 (SP-1) or higher.

Click here to install the latest Internet Explorer.
 

You need Office Spreadsheet components installed to see the example.

Notes:

If you need to lookup a value based on two or more values, check out:

  • VLOOKUP2 - lookup based on two columns
  • VLOOKUPX - lookup based on three columns

Published: 06-Feb-2004
Last edited: 24-Dec-2006 12:44