Tech & IT
Business
Coding & Developer
Finance & Accounting
Academics
Office Applications
Art & Design
Marketing
Health & Wellness
Sounds & Music
Lifestyle
Photography
This plan includes
- Limited free courses access
- Play & Pause Course Videos
- Video Recorded Lectures
- Learn on Mobile/PC/Tablet
- Quizzes and Real Projects
- Lifetime Course Certificate
- Email & Chat Support
This plan includes
- Access to 11,000+ Courses
- Ads free experience Courses
- Play & Pause Course Videos
HD Video Recorded Lectures- Learn on Mobile/PC/Tablet
- Quizzes and Real Projects
- Lifetime Course Certificate
- Instructor Direct Support
- Email & Chat Support
- Cancel Anytime
What you'll learn?
- Master the Dynamic array functions - UNIQUE, OFFSET, FILTER, SORT, SORTBY, RANDARRAY, and SEQUENCE function
- Discover the power of combining INDEX and MATCH functions for advanced lookup tasks.
- Combine SEQUENCE, RANDARRAY, SORTBY, INDEX to generate random array of data
- Mapping Data with VLOOKUP and INDIRECT function
- Understand how to use CHOOSE, TRANSPOSE, COLUMN, COLUMNS, ROW, ROWS functions
- Reverse a list with SEQUENCE, INDEX, ROWS functions
- Discover the beauty of using VLOOKUP and INDIRECT function
Course Overview
Hello and welcome to our comprehensive course on Master Excel's XLOOKUP, XMATCH, HLOOKUP, VLOOKUP, INDEX-MATCH! Right in this course, you will get to understand the New Excel 365/Excel 2021 Dynamic Array Functions - SORT function, FILTER function, RANDARRAY function, SEQUENCE function, UNIQUE function, XLOOKUP function, SORTBY function. I’m thrilled to have you join us on this exciting journey where we will dive deep into some of Excel’s most powerful and versatile functions. By the end of this course, you'll be able to handle Lookup tasks with ease, making you a more effective and efficient Excel user.
In this course, we'll cover the essential lookup functions such as XLOOKUP, XMATCH, HLOOKUP, VLOOKUP, and INDEX-MATCH, along with the dynamic array functions (SORT function, FILTER function, RANDARRAY function, SEQUENCE function, UNIQUE function, XLOOKUP function, SORTBY function) that revolutionize how we handle data in Excel. These functions are critical for anyone who works with large datasets, performs data analysis, or needs to extract specific information from complex tables. Whether you are a beginner aiming to solidify your foundational skills or an experienced user looking to master advanced techniques, this course will equip you with the knowledge to make data retrieval and analysis significantly easier and more efficient.
In this course you will:
-
Get an overview of how lookup functions streamline data retrieval and improve accuracy in data analysis.
-
Learn the syntax and structure of the VLOOKUP function.
-
Learn how to map data with VLOOKUP and INDEX function
-
Discover how to use VLOOKUP for both exact and approximate matches.
-
Understand the HLOOKUP function and its use cases.
-
Learn to retrieve data from horizontal tables with HLOOKUP.
-
Compare and contrast HLOOKUP with VLOOKUP to know when to use each function.
-
Dive into the new XLOOKUP function, a powerful replacement for VLOOKUP and HLOOKUP.
-
Understand how to manipulate and combine INDEX/MATCH functions and VLOOKUP/MATCH functions and VLOOKUP/INDIRECT functions and HLOOKUP/MATCH functions and more combinations of functions to maximize efficiency.
-
Understand how XLOOKUP simplifies complex lookups and provides greater flexibility.
-
Discover the power of combining INDEX and MATCH functions for advanced lookup tasks.
-
Learn how to use INDEX-MATCH for more dynamic and robust data retrieval compared to VLOOKUP.
-
Understand the advantages of INDEX-MATCH in terms of flexibility and performance.
-
Learn the benefits of dynamic arrays in handling and analysing large datasets.
-
Understand the synergy between XLOOKUP, INDEX-MATCH, and dynamic arrays for advanced data manipulation.
-
Explore key dynamic array functions such as FILTER, UNIQUE, SORT, SORTBY, SEQUENCE, and RANDARRAY.
Enrol now and take your Excel skills to the next level! Your path to becoming an Excel expert starts here!
Pre-requisites
- You must already know how to use Microsoft Excel
- You must already know how to write Microsoft Excel Formula and Functions
- You can use Excel 2021/365 software for this course
Target Audience
- This course is for those who want to Discover the power of INDEX-MATCH, the flexibility of XLOOKUP, the simplicity of VLOOKUP and HLOOKUP and the practical applications of dynamic array functions
- This course is for those who works a lot with the Lookup and Reference Functions
- This course is for those who want to understand the new dynamic arrays in Excel
Curriculum 81 Lectures 10:54:05
Section 1 : Introduction
Section 2 : Introduction to Basic Excel Dynamics
- Lecture 1 :
- Introduction to Excel - part 1
- Lecture 2 :
- Introduction to Excel - part 2
- Lecture 3 :
- How to Use the Search Bar in Office 2021
- Lecture 4 :
- Find, Replace, Wildcard
- Lecture 5 :
- Introduction to Excel - part 3
- Lecture 6 :
- Data Validation
- Lecture 7 :
- Creating data as a list
- Lecture 8 :
- Introduction to Formula and Basic Arithmetic Operators
- Lecture 9 :
- Logical Operators in Excel
- Lecture 10 :
- Concatenating in Excel
- Lecture 11 :
- Relative vs Absolute Referencing - Part 1
- Lecture 12 :
- Relative vs Absolute Referencing - Part 2
- Lecture 13 :
- Understanding Spill range and spill range reference - multiplication table
Section 3 : Creating an Array Formula Constants
- Lecture 1 :
- Array Formula - Part 1
- Lecture 2 :
- Array Formula - Part 2
Section 4 : INDEX-MATCH Functions
- Lecture 1 :
- INDEX-MATCH Functions
- Lecture 2 :
- MATCH function - Match type
- Lecture 3 :
- Nesting MATCH inside INDEX function - part 1
- Lecture 4 :
- Nesting MATCH inside INDEX function - part 2
Section 5 : CHOOSE, TRANSPOSE, COLUMN, ROW Functions
- Lecture 1 :
- CHOOSE, TRANSPOSE functions
- Lecture 2 :
- COLUMN, COLUMNS, ROW, ROWS functions
Section 6 : LOOKUP, VLOOKUP, HLOOKUP Functions
- Lecture 1 :
- LOOKUP, VLOOKUP functions
- Lecture 2 :
- VLOOKUP Function again
- Lecture 3 :
- Nesting MATCH function inside VLOOKUP
- Lecture 4 :
- HLOOKUP Function
- Lecture 5 :
- Absolute Referencing in table array using HLOOKUP
- Lecture 6 :
- MATCH and HLOOKUP combined
- Lecture 7 :
- Using VLOOKUP to find duplicate values in 2 array of data
Section 7 : Combining VLOOKUP and HLOOKUP Functions
- Lecture 1 :
- VLOOKUP and HLOOKUP combined - part 1
- Lecture 2 :
- VLOOKUP and HLOOKUP combined - part 2
Section 8 : ADDRESS, OFFSET, HYPERLINK, FORMULATEXT, INDIRECT functions
- Lecture 1 :
- ADDRESS Function
- Lecture 2 :
- OFFSET function
- Lecture 3 :
- HYPERLINK, FORMULATEXT, INDIRECT functions
Section 9 : Mapping data with VLOOKUP and INDIRECT Function
- Lecture 1 :
- Mapping Data with VLOOKUP and INDIRECT function - scenario 1
- Lecture 2 :
- Mapping Data with VLOOKUP and INDIRECT function - scenario 2
- Lecture 3 :
- Evaluating the VLOOKUP and INDIRECT mapping
Section 10 : VLOOKUP and RANDBETWEEN, INDEX and RANDBETWEEN Functions
- Lecture 1 :
- VLOOKUP and RANDBETWEEN - generate random data - Part 1
- Lecture 2 :
- VLOOKUP and RANDBETWEEN - generate random data - Part 2
- Lecture 3 :
- INDEX and RANDBETWEEN - to generate random data
Section 11 : XMATCH Function
- Lecture 1 :
- XMATCH Function
- Lecture 2 :
- XMATCH - search mode
Section 12 : XLOOKUP Function
- Lecture 1 :
- XLOOKUP - part 1
- Lecture 2 :
- XLOOKUP - part 2
- Lecture 3 :
- XLOOKUP - 2 Way Lookup
- Lecture 4 :
- XLOOKUP - [if not found] and [match mode]
- Lecture 5 :
- XLOOKUP with multiple criteria
- Lecture 6 :
- XLOOKUP with logical criteria
Section 13 : UNIQUE and FILTER Functions
- Lecture 1 :
- UNIQUE function
- Lecture 2 :
- FILTER function
- Lecture 3 :
- FILTER function on column data
Section 14 : SORT and SORTBY Functions
- Lecture 1 :
- SORT function
- Lecture 2 :
- SORT function - sort by columns - [by_cols] being TRUE
- Lecture 3 :
- SORTBY function
- Lecture 4 :
- Custom sort - SORTBY with LEN function
- Lecture 5 :
- Custom sort - SORTBY with MATCH function
- Lecture 6 :
- SORTBY with TEXT function - Custom sort dates
- Lecture 7 :
- SORTBY with MONTH or DAY functions - Custom sort dates
- Lecture 8 :
- SORTBY function on headings of your data
- Lecture 9 :
- SORTBY and COLUMN to reverse the order of a column list
- Lecture 10 :
- SORTBY and ROW to reverse the order of a row list
Section 15 : RANDARRAY Function
- Lecture 1 :
- RANDARRAY function
- Lecture 2 :
- RANDARRAY with CHAR to generate random alphabets
- Lecture 3 :
- RANDARRAY with DATE and EDATE functions
Section 16 : SEQUENCE Function
- Lecture 1 :
- SEQUENCE function - part 1
- Lecture 2 :
- SEQUENCE function - part 2
- Lecture 3 :
- ROUNDUP and SEQUENCE - repeated numbers
- Lecture 4 :
- MOD and SEQUENCE - repeated sequence
- Lecture 5 :
- SEQUENCE with TRANSPOSE, ROMAN, TEXT functions
- Lecture 6 :
- SEQUENCE with DATE and EDATE functions
- Lecture 7 :
- Reverse a list with SEQUENCE, INDEX, ROWS functions
- Lecture 8 :
- Reverse a list with SEQUENCE, SORTBY, ROWS functions
- Lecture 9 :
- Alphabetic sequence with SEQUENCE, CHAR
- Lecture 10 :
- Generate sequence of time - SEQUENCE, TIME and HOUR
- Lecture 11 :
- SEQUENCE, RANDARRAY, SORTBY, INDEX to generate random array of data - Part 1
- Lecture 12 :
- SEQUENCE, RANDARRAY, SORTBY, INDEX to generate random array of data - Part 2
- Lecture 13 :
- Filtering every nth value in a data - FILTER and MOD and SEQUENCE - part 1
- Lecture 14 :
- Filtering every nth value in a data - FILTER and MOD and SEQUENCE - part 2
- Lecture 15 :
- Dynamically creating a calendar - SEQUENCE and DATE and WEEKDAY - part 1
- Lecture 16 :
- Dynamically creating a calendar - SEQUENCE and DATE and WEEKDAY - part 2
- Lecture 17 :
- Dynamically creating a calendar - SEQUENCE and DATE and WEEKDAY - part 3
Our learners work at
Frequently Asked Questions
How do i access the course after purchase?
It's simple. When you sign up, you'll immediately have unlimited viewing of thousands of expert courses, paths to guide your learning, tools to measure your skills and hands-on resources like exercise files. There’s no limit on what you can learn and you can cancel at any time.Are these video based online self-learning courses?
Yes. All of the courses comes with online video based lectures created by certified instructors. Instructors have crafted these courses with a blend of high quality interactive videos, lectures, quizzes & real world projects to give you an indepth knowledge about the topic.Can i play & pause the course as per my convenience?
Yes absolutely & thats one of the advantage of self-paced courses. You can anytime pause or resume the course & come back & forth from one lecture to another lecture, play the videos mulitple times & so on.How do i contact the instructor for any doubts or questions?
Most of these courses have general questions & answers already covered within the course lectures. However, if you need any further help from the instructor, you can use the inbuilt Chat with Instructor option to send a message to an instructor & they will reply you within 24 hours. You can ask as many questions as you want.Do i need a pc to access the course or can i do it on mobile & tablet as well?
Brilliant question? Isn't it? You can access the courses on any device like PC, Mobile, Tablet & even on a smart tv. For mobile & a tablet you can download the Learnfly android or an iOS app. If mobile app is not available in your country, you can access the course directly by visting our website, its fully mobile friendly.Do i get any certificate for the courses?
Yes. Once you complete any course on our platform along with provided assessments by the instructor, you will be eligble to get certificate of course completion.For how long can i access my course on the platform?
You require an active subscription to access courses on our platform. If your subscription is active, you can access any course on our platform with no restrictions.Is there any free trial?
Currently, we do not offer any free trial.Can i cancel anytime?
Yes, you can cancel your subscription at any time. Your subscription will auto-renew until you cancel, but why would you want to?
Instructor
26 Course Views
1 Courses
This is Pamch Tutor. A tutoring genius who has been teaching ICT related courses since 2009 in my personal life.
I have a Masters Degree in Educational Technology (M.Ed. EdTech).
I am dynamic, friendly, creative, flexible and interactive with my teaching profession.
Teaching to me is not to pass time, it is what I breathe.
I have experience in teaching HTML, CSS, Bootstrap, JavaScript, Python,
Microsoft Excel, Scratch animation, etc.
Likewise, I train adults one-on-one on how to design websites, handbills, calendars,
business cards using Corel Draw.
As a trained and experienced teacher I value student's learning modalities;
hence, my lessons are driven to meet the student's needs and thus I frame a creative and possibility mentality.
Anyone who takes my courses know that with me there is no room for dull moments.
All my lessons are exciting, engaging and easy.
I simply make it simple because easy does it.
Great to have you on board.
