Find the closest match with the VLOOKUP function.

August 17, 2016

0 comments



We’re a team of problem solvers, world travelers, idea guys, and doers, working to make using Excel on a Mac a better experience.

That’s enough about us -- whether you are new to Excel for Mac or an experienced user, you probably want to spend less time manipulating data and more time thinking. The Excel Skin will help you do that. If you are a PC user hesitant about switching to Mac because you don’t want to give up your customized menus and shortcuts, well, the Excel Skin just might be the myth-buster you need. Excel for Mac is just as capable as it is for the PC. And with the Excel Skin, you’ll relearn your old shortcuts in no time.

The Excel Skin makes it easy to be as proficient and efficient in Excel for Mac as on any other platform. Powerful shortcuts are presented intuitively on an elegant silicone keyboard skin. Shortcut and modifier keys are printed by color and heat fused onto our high quality silicone to ensure durability.

The Excel Skin a small product that will make a big difference in your life.

 

In previous blog posts we learned how to use the VLOOKUP and HLOOKUP functions to find exact matches in a table. Today we are going to learn how to use the VLOOKUP function to find the closest match, instead of an exact match.

This is accomplished by changing the last part of the formula syntax, [range_lookup].  Setting this argument to FALSE returns only exact matches, but if set to TRUE, the returned value of the function will be the closest match.  Note, the table you are pulling data from must be in ascending order for the closest match to work correctly.

When would you want the closest match rather than an exact match?  Calculating grades is a great example for the closest match VLOOKUP. Keep in mind is the VLOOKUP matches the value that is less than or equal to the lookup value.  

 

So we have a score of 81, but we want to know the actual grade:

This searches the Scale table and finds that 81 is closest to a C, so it returns the letter C in the cell that we added our formula. 

 

And that's all there is to it!  Now you can use the VLOOKUP function to search for exact matches, or closest matches in your spreadsheet.

 

As always, please let us know if you have any questions! 



Leave a comment

Comments will be approved before showing up.