With the INDEX function, you can extract information from a range of cells by providing a row reference and a column reference. The function INDEX(A20:M105,15,10), for example, returns the content of the cell found at the intersection of the 15th row and 10th column inside the range A20:M105.
- [Voiceover] On this worksheet called INDEX,…we've got a table from cells B2 over and down to J7,…and it represents the prices for shipping packages…to different zones based on their size.…Possibly this is by weight, it could be…another indicator as well, now sometimes…we need to pull information out of the table…based simply on the column and row number.…So we've got some items listed over in column L,…their sizes, the shipping zone that they need to be sent to,…we want to know what the shipping cost is.…
So, this first example here,…size 5 represents data from this row here,…shipping zone 4 would represent data…in this column right here and so where…they intersect we're expecting a shipping cost…here of 31 dollars and 49 cents.…The INDEX function allows us to pull…information out of a table based…on a row and column reference, in that order.…=INDEX, left parenthesis.…We've got a table somewhere, it's in these cells right here.…
Notice that we're not highlighting…the labels on top and to the left of the data.…
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
-
Welcome50s
-
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: Retrieve information by location with INDEX