Tables associated with VLOOKUP can be large - not just a couple of columns, but many columns. The logic you use to indicate which column in a table has the desired answer will vary depending on the type of data in the table and can involve additional formulas to determine the appropriate column.
- [Voiceover] The more often you work with Excel functions…the more often you run into situations where you might…need to use two functions together.…There could even be times when you use the same function…twice, in what we call a nested form.…In this worksheet here, what we need to do first is…figure out which region each state is in.…There's a table in column J…that translates that information.…We need to use a V look up to figure out…which region each state is in.…Then based on that information, we'll be looking over in…that blue table to the right in columns M through U,…and also basing it on the number of dependents,…that's in column E, what is the appropriate tax rate…for this person.…
So, let's use a V look up…first of all to determine which region each state is in.…This is going to be based on the table in columns J…and K.…Equal V look up.…Left parenthesis.…Here's the item we're looking up, in this case it's…Florida, comma.…We're looking in columns J and K,…so I can cross those column letters that way.…
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 nested lookup functions