Just as you can nest IF functions (IF functions within IF functions), you can also nest lookup functions. These might be required when the result of one lookup function is needed as the item to be looked up as you use it in another lookup function - either with exact match or approximate match lookups.
- [Voiceover] We're looking at a worksheet…called large table vlookup, and we want to come up…with the tax rate for each person here.…And the tax rate's based on the person's salary…and the number of dependents.…So, unlike a lot of look-up tables…that are only two or three columns wide,…this is eight columns wide.…It goes all the way down to row 18.…So, let's use the vlookup here…to look up some information.…Sometimes when you use vlookup,…there are no hard and fast rules,…in other words, you'll have to use your own logic,…to come up with the result.…That's going to be the case in this example.…
Equal vlookup, left parenthesis.…We've got a salary in cell B2,…that's what we're trying to look up in that table.…Comma, rather than highlighting all the cells…within the table, you can simply drag across…the letter columns here, provided we know for sure…nothing else will occur anywhere else in these columns…except for this table.…So, columns F through M, comma.…Now, we need to get data out of a different column…depending upon how many dependents a person has.…
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: Use VLOOKUP with large tables