Find exact matches with VLOOKUP

Keyboard Shortcuts

If using VLOOKUP to find exact matches, the item to be matched must be text or an id-type number and the function contain four arguments - 1. The value being looked up, 2. The table's location, 3. the column of the table containing the answers, and 4. FALSE or zero to specify that the match must be exact.

- [Voiceover] On this worksheet called ExactVLOOKUP,…we'd like to convert these ratings in column A…into numerical scores…based on the Rate Table that we see over in columns A and B.…Now, in an earlier movie, you may have seen how…to do an approximate VLOOKUP.…In this case and any time we're trying…to match up text entries,…we need to use VLOOKUP in a different way.…We need this to be an exact match…and, if we're looking up information based on ID numbers,…social security numbers, those must be exact match.…

So for some people, their use of VLOOKUP might…be much more commonly revolving around the idea…of it must be an exact match.…Now, in cell F2, I've got a VLOOKUP function set up already.…It's looking up data in the table over in columns A and B…as trying to get answers out of the second column…and everything we see here…is accurate except something is missing…and you wouldn't know that instinctively.…The answer we're getting here, based on the table,…is not correct.…It's 99 and, if we were going to copy this down the column,…

Resume Transcript Auto-Scroll

Author

Released

5/26/2016

Conquer some of the most daunting features in Microsoft Excel: formulas and functions. In this Excel for Mac 2016 update to his popular series, author Dennis Taylor presents numerous formulas and functions in Excel and shows how to use them efficiently. The course begins with tips and keyboard shortcuts to accelerate the way you work with formulas within one or multiple worksheets. Dennis then covers how to perform logical tests with the IF, AND, OR, and NOT functions; search and retrieve data with lookup functions (VLOOKUP, MATCH, and INDEX); analyze data with statistical functions; use text functions to clean up worksheets; work with array formulas and functions, and master date and time calculations. Dennis focuses on practical examples that transition effortlessly to real-world scenarios. Start watching to take advantage of the tremendous power offered by Excel formulas and functions.

Topics include:

Understanding how the hierarchy of operators affects formula results

Knowing when to use absolute vs. relative references

Using Formula Builder for unfamiliar functions

Displaying a worksheet's formulas and highlighting formula cells

Converting formulas to values

Creating 3D formulas to gather data from multiple sheets

Creating and expanding nested IF functions; using AND, OR, and NOT with IF

Looking up information with VLOOKUP, HLOOKUP, MATCH, and INDEX

Analyzing data with the statistical functions: MEDIAN, MODE, etc.

Using the power functions: COUNTIF, SUMIF, COUNTIFS, AVERAGEIFS, and more