Understand Excel's XLOOKUP, HLOOKUP, VLOOKUP, INDEX-MATCH

Focused View

Pamch Tutor M.Ed

10:54:06

0 View

1. Introduction

1. Introduction.mp4

12:16

2. Introduction to Basic Excel Dynamics

1. Introduction to Excel - part 1..mp4

12:25

2. Introduction to Excel - part 2.mp4

05:33

3. How to Use the Search Bar in Office 2021.mp4

03:17

4. Find, Replace, Wildcard.mp4

12:24

5. Introduction to Excel - part 3.mp4

03:00

6. Data Validation.mp4

11:30

7. Creating data as a list.mp4

04:19

8. Introduction to Formula and Basic Arithmetic Operators.mp4

10:35

9. Logical Operators in Excel.mp4

05:03

10. Concatenating in Excel.mp4

03:23

11. Relative vs Absolute Referencing - Part 1.mp4

07:32

12. Relative vs Absolute Referencing - Part2.mp4

08:21

13.1 spill range multiplication table.xlsx

13. Understanding Spill range and spill range reference - multiplication table.mp4

06:24

3. Creating an Array Formula Constants

1. Array Formula - Part 1.mp4

10:20

2. Array Formula - Part 2.mp4

13:58

4. INDEX-MATCH Functions

1.1 Lookup-Dynamic Array Files.zip

1. INDEX, MATCH functions.mp4

09:27

2. MATCH function - Match type.mp4

03:39

3. Nesting MATCH inside INDEX function - part 1.mp4

03:21

4. Nesting MATCH inside INDEX function - part 2.mp4

08:29

5. CHOOSE, TRANSPOSE, COLUMN, ROW Functions

1. CHOOSE, TRANSPOSE functions.mp4

03:11

2. COLUMN, COLUMNS, ROW, ROWS functions.mp4

03:45

6. LOOKUP, VLOOKUP, HLOOKUP Functions

1. LOOKUP, VLOOKUP functions.mp4

08:18

2. VLOOKUP Function again.mp4

04:01

3. Nesting MATCH function inside VLOOKUP.mp4

08:31

4. HLOOKUP Function.mp4

11:33

5. Absolute Referencing in table array using HLOOKUP.mp4

04:50

6. MATCH and HLOOKUP combined.mp4

08:48

7. Using VLOOKUP to find duplicate values in 2 array of data.mp4

06:30

7. Combining VLOOKUP and HLOOKUP Functions

1. VLOOKUP and HLOOKUP combined - part 1.mp4

10:02

2. VLOOKUP and HLOOKUP combined - part 2.mp4

07:47

8. ADDRESS, OFFSET, HYPERLINK, FORMULATEXT, INDIRECT functions

1. ADDRESS Function.mp4

03:16

2. OFFSET function.mp4

18:15

3. HYPERLINK, FORMULATEXT, INDIRECT functions.mp4

12:50

9. Mapping data with VLOOKUP and INDIRECT Function

1. Mapping Data with VLOOKUP and INDIRECT function - scenario 1.mp4

15:27

2. Mapping Data with VLOOKUP and INDIRECT function - scenario 2.mp4

09:17

3. Evaluating the VLOOKUP and INDIRECT mapping.mp4

03:42

10. VLOOKUP and RANDBETWEEN, INDEX and RANDBETWEEN Functions

1. VLOOKUP and RANDBETWEEN - generate random data - Part 1.mp4

05:28

2. VLOOKUP and RANDBETWEEN - generate random data - Part 2.mp4

06:39

3. INDEX and RANDBETWEEN - to generate random data.mp4

10:42

11. XMATCH Function

1. XMATCH Function.mp4

12:00

2. XMATCH - search mode.mp4

11:56

12. XLOOKUP Function

1. XLOOKUP - part 1.mp4

08:07

2. XLOOKUP - part 2.mp4

07:31

3. XLOOKUP - 2 Way Lookup.mp4

17:37

4. XLOOKUP - [if not found] and [match mode].mp4

09:34

5. XLOOKUP with multiple criteria.mp4

09:52

6. XLOOKUP with logical criteria.mp4

08:29

13. UNIQUE and FILTER Functions

1. UNIQUE function.mp4

09:48

2. FILTER function.mp4

14:30

3. FILTER function on column data.mp4

05:57

14. SORT and SORTBY Functions

1. SORT function.mp4

11:26

2. SORT function - sort by columns - [by cols] being TRUE.mp4

11:40

3. SORTBY function.mp4

10:00

4. Custom sort - SORTBY with LEN function.mp4

05:27

5. Custom sort - SORTBY with MATCH function.mp4

10:30

6. SORTBY with TEXT function - Custom sort dates.mp4

09:08

7. SORTBY with MONTH or DAY functions - Custom sort dates.mp4

04:43

8. SORTBY function on headings of your data.mp4

01:28

9. SORTBY and COLUMN to reverse the order of a column list.mp4

03:26

10. SORTBY and ROW to reverse the order of a row list.mp4

04:28

15. RANDARRAY Function

1. RANDARRAY function.mp4

08:16

2. RANDARRAY with CHAR to generate random alphabets.mp4

04:34

3. RANDARRAY with DATE and EDATE functions.mp4

06:31

16. SEQUENCE Function

1. SEQUENCE function - part 1.mp4

07:19

2. SEQUENCE function - part 2.mp4

03:22

3. ROUNDUP and SEQUENCE - repeated numbers.mp4

05:49

4. MOD and SEQUENCE - repeated sequence.mp4

10:07

5. SEQUENCE with TRANSPOSE, ROMAN, TEXT functions.mp4

05:00

6. SEQUENCE with DATE and EDATE functions.mp4

08:16

7. Reverse a list with SEQUENCE, INDEX, ROWS functions.mp4

09:31

8. Reverse a list with SEQUENCE, SORTBY, ROWS functions.mp4

02:07

9. Alphabetic sequence with SEQUENCE, CHAR.mp4

01:54

10. Generate sequence of time - SEQUENCE, TIME and HOUR.mp4

05:25

11. SEQUENCE, RANDARRAY, SORTBY, INDEX to generate random array of data - Part 1.mp4

09:44

12. SEQUENCE, RANDARRAY, SORTBY, INDEX to generate random array of data - Part 2.mp4

10:49

13. Filtering every nth value in a data - FILTER and MOD and SEQUENCE - part 1.mp4

15:25

14. Filtering every nth value in a data - FILTER and MOD and SEQUENCE - part 2.mp4

05:16

15. Dynamically creating a calendar - SEQUENCE and DATE and WEEKDAY - part 1.mp4

18:11

16. Dynamically creating a calendar - SEQUENCE and DATE and WEEKDAY - part 2.mp4

06:42

17. Dynamically creating a calendar - SEQUENCE and DATE and WEEKDAY - part 3.mp4

04:03

Description

Learn problem-solving skills with Dynamic Arrays (SEQUENCE, SORTBY, FILTER,etc), XLOOKUP, HLOOKUP, VLOOKUP, INDEX-MATCH

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.
Learn the versatile features of XLOOKUP, XMATCH
Combine SEQUENCE, RANDARRAY, SORTBY, INDEX to generate random array of data
Dynamically creating a calendar - SEQUENCE and DATE and WEEKDAY
Mapping Data with VLOOKUP and INDIRECT function
Discover the beauty of using VLOOKUP and INDIRECT function
Learn to retrieve data from horizontal tables with HLOOKUP.
Discover how to use VLOOKUP for both exact and approximate matches.
Understand how to use CHOOSE, TRANSPOSE, COLUMN, COLUMNS, ROW, ROWS functions
Reverse a list with SEQUENCE, INDEX, ROWS functions

Who is this for?

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

What You Need to Know?

You must already know how to use Microsoft Excel

You must already know how to write Microsoft Excel Formula and Functions

You SHOULD use Excel 2021/365 software for this course

More details

Description
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!
Who this course is for:
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

User Reviews

Rating

average 0

Total votes0

Focused display

Excel Formulas and Functions

Pamch Tutor M.Ed

Instructor's 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.

Udemy

View courses Udemy

Students take courses primarily to improve job-related skills.Some courses generate credit toward technical certification. Udemy has made a special effort to attract corporate trainers seeking to create coursework for employees of their company.