Learn how to use the HLOOK and VLOOKUP functions. Author Jen McBee demonstrates how to create a formula using the HLOOKUP and the VLOOKUP functions, focusing on methods you will be asked to demonstrate on the Excel 2013 MOS Expert exam.
- [Instructor] We're going to continue discussing…the lookup functions available in Excel 2013.…In this video, we'll focus on the HLOOKUP…and the VLOOKUP functions.…The HLOOKUP function is a horizontal lookup.…So if your information is laid out in a horizontal pattern…such as you have your headings in row 1…and your data begins in row 2 and we need Excel…to look horizontally or from left to right for the value,…we'd use the HLOOKUP.…
However, if your information is laid out in columns…and we want Excel to be able to look up and down…or vertically to find the result,…we would use the VLOOKUP formula.…Let's go ahead and jump into our…04_02 HLOOKUP_VLOOKUP document.…We have four worksheets that we'll be working with.…The first one is our 2017 Student Roster.…It contains the student's ID,…name, state, phone, email,…and the program in which they're enrolled.…
On our second worksheet, we want to be able to pull all…of the photography students using a VLOOKUP.…We do know their ID number, we just don't know their name.…
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.