Vlookup with a Range of Numbers in Excel

Add to Favorites

How to use the Vlookup function to return a result that falls within a range of numbers, such as a weight or quantity or even tests or grades.

This allows you to, for example, return the letter A for a score of 90 - 100 on a test.

Here, it is assumed that you are at least partly familiar with the Vlookup function.  If you want to learn more about it, check out our Vlookup tutorial.

Steps to use the Vlookup Function with a Range of Numbers

  1. Make sure that the data table that we want to search through is sorted in ascending order, which means that it is sorted lowest-to-highest.  Also, the value that we use to search through the table must be in the left-most column of the table.
  2. Enter the Vlookup function:

    =VLOOKUP(D2,A1:B5,2)

    D2 is the cell that contains the lookup value or the value that we will use to search through the table to return a value.

    A1:B5 is the table where we will search for the value.

    2 is the number of the column in the lookup table from where we want to return a value.  1 would be the first column of the lookup table, which is also the column that contains the lookup values.

    We do not need to input anything in for the range_lookup argument, the fourth argument of the function.  However, if you want, you can enter TRUE for that argument, which is the default value.

  3. Test it out.


As you can see, as long as you are familiar with the Vlookup function, this is a very simple thing to do in Excel.  The most difficult part is to remember to sort the lookup table in ascending order.

Make sure to download the workbook that accompanies this tutorial so you can see this in action.


Excel Function: VLOOKUP()
Downloadable Files: Excel File

Similar Content on TeachExcel
Limit the Total Amount a User Can Enter into a Range of Cells in Excel
Tutorial: How to limit the amount that a user can enter into a range of cells in Excel.  This works ...
Loop through a Range of Cells in Excel VBA/Macros
Tutorial: How to use VBA/Macros to iterate through each cell in a range, either a row, a column, or ...
Count The Number of Words in a Cell or Range of Cells in Excel - With User-Specified Delimiter / Separator - UDF
Macro: UDF to count the number of words in a cell or range with a user-specified delimiter. ...
Count The Number of Words in a Cell or Range of Cells in Excel - UDF
Macro: Count words in cells with this user defined function (UDF). This UDF allows you to count t...
Require a Unique List of Numbers in a Range in Excel
Tutorial: I'll show you how to require a user to enter a unique number into a range of cells in Exce...
Quickly Create a Huge List of Numbers in Excel
Tutorial: Quickly create a large list of numbers in Excel using the Fill Command.  This will save y...