Finding approximate matches (only used with numbers) with lookup functions requires data in the left column (or top row) be in ascending order. Both functions need to use three arguments - 1. The value being looked up, 2. The location of the table, and, 3. the column of the table containing the answers.
- [Voiceover] In this worksheet called…"approximate vlookup",…we're going to be using the vlookup function,…in cell I2, and then down the column,…to look up information in this table right here.…We could be using the other table to the right,…if we were to use this horizontally oriented table,…we would be using the function called "hlookup",…h for horizontal.…When you use the vlookup function,…and the hlookup function,…you always want to be thinking:…"Is this an exact match?…Do I have to match something exactly?",…and many times you will.…
Anytime you're matching ID numbers,…like social security numbers,…people's names, any kind of text entry,…it must be exact.…In this scenario here,…we've got a compensation amount in column H,…and here we're not trying to find the number exactly,…in fact it's quite likely that none of these…numbers matches exactly the numbers that we see…in this table here.…Now, by definition, vlookup means:…compare value, for example the 70,000,…with entries in the left column of a table.…
A table might have two columns, three, five, eight,…
Author
Dennis Taylor
Released
5/26/2016- 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
- Calculating financial data such as payments
- Performing basic math
- Calculating dates and times
- Editing text with functions
- Using array formulas and functions
- Referencing data in other cells and files
- Extracting information from Excel worksheets
Skill Level Intermediate
Duration
Views
-
Introduction
-
Welcome46s
-
Exercise files25s
-
-
1. Formula and Function Tools
-
2. Formula and Function Tips and Shortcuts
-
3. IF and Related Functions
-
4. Lookup and Reference Functions
-
Use MATCH and INDEX together4m 40s
-
5. Statistical Functions
-
6. Power Functions
-
7. Selected Financial Functions
-
8. Math Functions
-
9. Date and Time Functions
-
10. Text Functions
-
11. Array Formulas and Functions
-
12. Reference Functions
-
13. Information Functions
-
Conclusion
-
Next steps25s
-
Take notes with your new membership!
Type in the entry box, then click Enter to save your note.
Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.
Share this video
Embed this video
Video: Find approximate matches with VLOOKUP