Subscribe for Weekly Tutorials
BONUS: subscribe now to download our Top Tutorials Ebook!
Vlookup with a Range of Numbers in Excel
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
- 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.
- 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.
- 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.
Question? Ask it in our Excel Forum
Tutorial: How to limit the amount that a user can enter into a range of cells in Excel. This works...
Tutorial: How to use VBA/Macros to iterate through each cell in a range, either a row, a column, or ...
Macro: UDF to count the number of words in a cell or range with a user-specified delimiter. ...
Tutorial: I'll show you how to require a user to enter a unique number into a range of cells in Exce...
Macro: Count words in cells with this user defined function (UDF). This UDF allows you to count t...
Tutorial: How to loop through a range of cells in a UDF, User Defined Function, in Excel. This is ...