XLOOKUP is a newer function that surpasses the awesomeness of VLOOKUP, but it’s only available in the Office 365 (Excel 2019) versions (and newer as they’re developed). There is still great value in VLOOKUP (and HLOOKUP as well), it just takes nesting more functions. Read the XLOOKUP post to learn more about it.
Manually eyeball a value
Imagine you’re judging some random contest. Participants earn different colored ribbons based on how many points they earn. You have a table that you use to know which color to award for a minimum number of points.
Look at the values in the table to the right. What color ribbon would you award someone earning 789 points? Yellow, right?
You looked in the Min column for 789 and see they earn a Yellow ribbon because they didn’t earn enough points to get the Red ribbon.
Plug this information into Excel
(FYI: you can replace Google Sheets anytime the word Excel is used) Plugging the information into Excel, it could look like the image below.
When you eyeball this, you look at what is in D2 and try to find that number in the range from A2:B6. You find the result in the first column, and see ribbon color in the second column. You manually type “Red” into cell E2.
Vlookup function
Excel can do this with the VLOOKUP function. (Think V, as in Vertical)
In order for Excel to know what your eyeball saw, you’ll provide information into the function using arguments.
The VLOOKUP function has three required arguments:
=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])
- lookup_value: What value to find
- table_array: Where that value and the results are found
Excel knows to look for the lookup_value
in the first (vertical) column of the table_array - col_index_num: Which column in the table_array
has the result
[range_lookup]: The fourth argument is optional. Optional arguments are listed in brackets. You don’t need this argument for an approximate lookup
When you put it all together, it looks like this:
KEY POINT!
When using VLOOKUP to find an approximate value (as in this example), the first column of the table_array must be sorted in ascending order (smallest to largest) or it flat out doesn’t work.
It is therefore mandatory your table_array is sorted by the first column; in ascending order.
As always, we’re available to help you with your individual questions. With over twenty years of experience, we are proud to have clients in many industries including: Real Estate, construction, manufacturing, non-profits, and research and development.
You can reach us by calling or texting 775 . 525 . 0885 or emailing info [at] MyRenoComputerTutor [dot] com
We’re dedicated to helping people understand what they’re doing. So please reach out. You can contact us via email, or voicemail, and you can even text us to get your training scheduled. Diana also trains for Washoe County, City of Reno and UNR Extended Studies.
