Learn how to nest the MATCH function with INDEX. Author Jen McBee demonstrates how to use these functions to first find the row on which the value is located, and then nest that information in an INDEX function to get the desired result.
- [Instructor] Very often we need to use Excel's functions…to look up a specific value for us,…and most of the time we use the HLOOKUP…or the VLOOKUP, and that works just fine.…But those lookup functions can only search…one row or one column of information at a time.…In this video I'm going to show you…how you can use the MATCH function in conjunction…with the INDEX function to search through…an entire array of information to find…the information you're looking for.…
The MATCH function actually searches…through the column and will return…the row number where the value is located.…The INDEX function will search through an entire array…and will use the row number that we find…in our MATCH function to be able to…return the correct value to us.…We'll create our MATCH function first,…and we will nest the MATCH function…right into the INDEX function.…I have the 04_03 LOOKUP_Match_Index document open,…so let's get started.…
Our goal is to find out what the Q4 result is…for Catering Dinners.…We can see that we have an item number,…
The course begins with an overview of the certification program and its costs. Next, Jennifer walks through all of the certification objectives, including hands-on experience with downloadable sample documents, so you can practice as you go. She wraps up with a full-length practice test that emulates exam 77-427, together with solutions to each of the exam challenges.
- Reviewing the exam format and learning objectives
- Tracking, retaining, and displaying all changes
- Identifying and troubleshooting errors with tracing
- Applying custom formats and layouts
- Applying advanced conditional formatting
- Using functions to format cells and apply advanced filters
- Preparing a workbook for internationalization
- Creating advanced formulas
- Using the LOOKUP and TRANSPOSE functions
- Using functions to serialize dates and times
- Creating advanced charts and tables
Skill Level Advanced
1. Microsoft Office Specialist (MOS) Expert Exam Overview
2. Manage and Share Workbooks
Manage comments2m 57s
3. Apply Custom Formats and Layouts
4. Create Advanced Formulas
5. Create Advanced Charts and Tables
6. Full-Length MOS Expert Sample Exam for Excel 2013
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.Cancel
Take notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press 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.