Use the CHOOSE function, instead of a lookup function when the table is sufficiently small to store its contents within the function. The function to find the fiscal quarter =CHOOSE(MONTH(B2), 1,1,1,2,2,2,3,3,3,4,4,4) has the table contents built into it so there's no need for an external table.
- [Voiceover] On this worksheet called Choose,…we've got some dates in column A, and in column B…is a formula that calculates the quarter of the year…for each of these.…Probably the kind of formula a mathematician might use…and there's certainly nothing wrong with it.…It does give us answers, and those answers are correct.…However, sometimes it's worth using a function…that might be a little bit longer,…but probably is easier to understand for others…who might not have a mathematical background.…The function is called Choose.…And in some ways it's like a vlookup or an hlookup,…except there's no external table.…
The data is within the actual function itself.…In this formula in cell B2,…and you don't have to completely understand it,…it does work.…But it is also using a function called Month,…and we'll be using this too, as well,…inside of a Choose function.…The Month function simply calculates the month,…that's from one to 12,…for a given date, that's located in a different location.…In the example here, the value 12…
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 table-like information with CHOOSE