UrbanPro
true
Mohammad Khalid Microsoft Excel trainer in Rampurhat

Featured

Mohammad Khalid

Personal and Corporate Excel Trainer | Trained +500 Professionals Worldwide

Santipara Rd Railway Colony, Rampurhat, India - 731224.

Verified 5 Students

Book a Demo
Referral Discount: Get ₹ 500 off when you make a payment to start classes. Get started by Booking a Demo.

Details verified of Mohammad Khalid

Identity

Education

Know how UrbanPro verifies Tutor details

Identity is verified based on matching the details uploaded by the Tutor with government databases.

Overview

I am an expert in Microsoft Excel and available to provide online tutoring. With a focus on efficiency and practical learning, I can help you master Excel's functionalities quickly and effectively. I've the experience of teaching students in very effective way.

Intro Video

  • Microsoft Excel Training

+2 more

Languages Spoken

Bengali Mother Tongue (Native)

English Proficient

Hindi Proficient

Urdu Proficient

Education

Burdwan University 2020

Bachelor of Arts (B.A.)

Indira Gandhi National Open University 2023

Post Graduation Diploma in Mass Communication and Journalism

Learn with Lokesh 2023

Excel Masterclass Beginner to Advanced

Address

Santipara Rd Railway Colony, Rampurhat, India - 731224

Verified Info

ID Verified

Education Verified

Phone Verified

Email Verified

Taught Students from these Schools

Report this Profile

Is this listing inaccurate or duplicate? Any other problem?

Please tell us about the problem and we will fix it.

Please describe the problem that you see in this page.

Type the letters as shown below *

Please enter the letters as show below

Teaches

Microsoft Excel Training classes
1 Student

Class Location

Online Classes (Video Call via UrbanPro LIVE)

Student's Home

Tutor's Home

Years of Experience in Microsoft Excel Training classes

1

Teaches following Excel features

Advanced Excel, Basic Excel, Excel Macro Training

Teaching Experience in detail in Microsoft Excel Training classes

Master Microsoft Excel - Advanced.

 

What you'll learn...

1. Learn all about syntax, arguments and logic.

2. How to create custom and nested functions.

3. Learn the fastest and smartest ways of cleaning the raw data

4. Create dynamic reports by mastering one of the most popular tools, PivotTables.

5. Learn which chart/graph to use and when.

6. How to automate repetitive tasks in Excel using Macros.

7. Learn various 'must have' Excel shortcuts.

8. Learn how to manage heavy Excel files.

9. Amazing Bonus tricks that save tons of time and effort.

 

This Course will include

1. Live ZOOM Classes

2. Recordings of the Previous Classes (In case You Can't Attend LIVE Classes)

4. One Live QnA Session every Week to clear all your doubts

5. Downloadable Excel Files for Practice

6. Access on Laptop, mobile and TV

 

Course Duration - 29 days (can be customized accordingly)

Language - Hindi, English, Bengali (Any Language Preferred by Student)

 

Here is my Detailed Course Plan and Chapters

 

LOOKUP FUNCTIONALITY (BURN SIMPLE FIND TECHNIQUE)

 

Vlookup (Exact Match) # 1

Exercise Vlookup (Exact Match) # 1

Type of References (Use of $ Sign)

Vlookup (Exact Match) # 2

Exercise Vlookup (Exact Match) #2

Double Vlookup

Exercise Double Vlookup

Vlookup on Duplicate Values

Exercise Vlookup on Duplicate Values

Vlookup (Approximate Match)

Exercise Vlookup (Approximate Match)

Vlookup with IF (Conditional Vlookup)

Exercise Vlookup with IF (Conditional Vlookup)

Hlookup (Exact Match)

Exercise Hlookup (Exact Match)

Hlookup (Approximate Match)

Exercise Hlookup (Approximate Match)

Lookup (Don't Use This)

Exercise Lookup (Don't Use This)

Vlookup & Match (Create magical Vlookup)

Exercise Vlookup & Match ( Create a Magical Vlookup)

Match (Gives us Column & Row Number)

Exercise Match (Gives us Column & Row Number)

Index & Match (Made for each other)

Exercise number 1 of Index & Match (Made for each other)

Exercise number 2 of Index & Match (Made for each other)

Excel Files for Practice (Please Download)

 

DATE & TIME FUNCTION (A TRICKY GAME)

How Excel Records Date & Timer

Now, Today & Autofill

DateValue & TimeValue

Calculate WorkingDays

DatedIf

Excel Files for Practice (Please Download)

 

FINANCIAL FUNCTIONS (USEFUL FOR NON-FINANCE GUYS TOO)

 

Loan Calculation (PMT, PPMT, IPMT etc.)

Ex Loan Calculation (PMT, PPMT, IPMT etc.)

Creating a Loan Table

Ex Creating a Loan Table

Calculate Depreciation

Ex Calculate Depreciation

Excel Files for practice (Please Download)

 

SORT & FILTER (COMMON FOR ALL)

 

Basic Sorting & Shortcut keys

Ex Basic Sorting & Shortcut Keys

Sorting Using Custom List

Ex Sorting Using Custom List

Horizontal Sorting

Ex Horizontal Sorting

Basic Filter & Shortcut Keys

Ex Basic Filter & Shortcut Keys

Filter Problem # 1 (Copy Filtered Data without hidden rows)

Ex Filter Problem # 1 (Copy Filtered Data without hidden Rows)

Filter Problem # 2 (Pasting Values on Filtered Data)

Ex Filter Problem # 2 (Pasting Values on Filtered Data)

Filter Problem # 3 (Performing Calculation on Filtered Data)

Ex Filter Problem # 3 (Perform Calculations on Filtered Data)

Advanced Filter (Using Complex Criteria)

Ex Advanced Filter (Using Complex Criteria)

Excel Files for Practice (Please Download)

 

EXCEL CHARTS (ITS TIME FOR VISUAL PRESENTATION)

 

1. Excel Charts Introduction

2 How to Create a Chart

3 Link a Chart Title

4 How to Show Axis Title

5 How to Show Data Label

6 Column Chart

7 Bar Chart

8 Line Chart 1

9 Line Chart 2

10 Area Chart

11 Pie Chart

12 Pie of Pie or Bar of Pie

13 Line Chart with Log Scale

14 Bubble Chart

15 Selecting Chart Elements

16 Use of Format Dialog box

17 Modifying Chart & Plot Area

18 Modifying Data Series

19 How to handle Missing Data

20 Modifying 3-D Charts

21 Creating Chart Templates

22 Creating Picture Charts

23 Creating a Combination of upto 5 Different Charts

24 Show data with the chart in Data Table

25 Play with the Design & Layout of the Chart

Excel Files for Practice (Please Download)

 

DATA CONSOLIDATION (MULTIPLE WORKBOOKS WORKSHEETS)

 

Consolidate Data using Excel Inbuilt Function

Ex Consolidate data using Excel Inbuilt Function

Excel Files for Practice (Please Download)

 

DATA VALIDATION

 

Applying Data Validation

Ex Applying Data validation

Dependent Dropdown List

Ex Dependent Dropdown List

Using Data Validation with Vlookup & Match

Other Data Validation Examples

Excel Files for Practice (Please Download)

 

PROTECTION OF WORKSHEET WORKBOOK

 

Worksheet Level Protection

Ex Worksheet Level Protection

Cell Level Protection

Ex Cell Level Protection

Hiding Formulas from Formula Bar

Ex Hiding Formulas from Formula Bar

Protecting Workbook Structure

Ex Protecting Workbook Structure

Workbook Level Protection

Ex Workbook Level Protection

Excel Files for Practice (Please Download)

 

PIVOT TABLE & TECHNIQUES

 

Understanding pivot Table

Using Pivot Table in Real Scenario

Ex Using Pivot Table in Real Scenario

Grouping in Pivot Table

Ex Grouping in Pivot Table

Using Slicers (A Visually attractive Filter)

Ex Using Slicers (A Visually attractive Filter)

Excel Files for Practice (Please Download)

 

WHAT-IF ANALYSIS & SOLVER ADD-IN

 

Goal Seek (A Reverse Approach)

Ex Goal Seek (A Reverse Approach)

One-Way Data Table

Two-Way Data Table

Scenario Manager

Solver Add-In

Excel Files for Practice (Please Download)

 

16 AUTOMATION IN EXCEL (MACROS)

 

Record your first Macro

Record Another Macro

Assigning Macros to a Shape, Button, Picture

Editing or Deleting a Macro

Security Settings of a Macro

Excel Files for Practice (Please Download)

 

17. New Video Tutorials

 

*How to create a Map Chart in Excel

*How to use the Forecast sheet in Excel

*Excel Files for Practice (Please Download)

 

New Chapter - Conditional Formatting

 

*Highlight cells rules

*Top Bottom Rules

*Data Bars

*Color Scales

*Icon Sets

*Function based Conditional Formatting

*Exercise files for practice (please download)

 

Bonus Videos (In Hindi)

*Why my excel file size is huge and how to reduce Excel file size

*Problem of unwanted names when we create a copy of the sheet in Excel

*New chart types in Excel 2019/Office 365 || Map Chart || Funnel Chart

*How do you change the number format in Excel?

 

Types of Errors in Excel and How to resolve them

 

Excel Files for Practice (Please Download)

 

Other Experiences -

1. Taught, Motivated Students

2. Public Speaker

3. Conducted Webinars

4. Conducted Students Career Counseling

 

Let's discuss your specific needs and schedule a session at your convenience.

 

Best regards,

Md Khalid

Documents (1)

EXCEL MASTERCLASS: BEGINNER TO ADVANCED...

Reviews (2)

5 out of 5 2 reviews

Mohammad Khalid https://urbanproprod.blob.core.windows.net/tv-prod/member/photo/11910877-small.jpg Santipara Rd Railway Colony
5.0052
Mohammad Khalid
A

Microsoft Excel Training

"Teaching style of Khalid sir is very nice. I didn't even opened Ms office applications before I got his training. now I'm expert in Ms excel. He makes you understand complex functions in very simple way. "

Reply by Mohammad

Thanks for your Review.

Mohammad Khalid
J
Verified Student

Excel Masterclass: Beginner to Advanced

I loved

Audio/Video Quality

Teaching Method

Teacher's Knowledge

Have you attended any class with Mohammad?

FAQs

1. What Excel features do you teach?

Advanced Excel, Basic Excel and Excel Macro Training

2. Which classes do you teach?

I teach Microsoft Excel Training Class.

3. Do you provide a demo class?

Yes, I provide a free demo class.

4. How many years of experience do you have?

I have been teaching for 1 year.

Answers by Mohammad (2)

Answered on 23 May Learn IT Courses/MS Office Software Training/Microsoft Excel Training/Excel INDEX

let me explain you the data first. Here the data is that we have a serial number, we have the code for a particular expense and the expenses are given for the complete year. Jan, Feb, March and the quarter total are also given. Now, we have this small table in front of us in which we have a list of... ...more

let me explain you the data first. Here the data is that we have a serial number, we have the code for a particular expense and the expenses are given for the complete year. Jan, Feb, March and the quarter total are also given.

Now, we have this small table in front of us in which we have a list of expenses as well as for which particular column we need to find out the value for. First we need to find out the code for grocery, home phone, cable TV and tuition. Same we need to find out the expenses related to these expenses, these items in quarter 2, 3 and 4. If you see one thing that we are talking about code right now.

If you look at it, expenses are on column D and technically this is the third column in the data and code is the second column. So, this is basically a reverse pre-lookup or reverse lookup we are talking about. Now here, first I am going to show you that what INDEX do.

You know about the MATCH already. The MATCH function is that if I assign a MATCH function, just have a look. First I am going to break down the formula for you to make it easier for you to understand and then we will club it in a single cell.

So have a look. I am going to use MATCH first and then we will club it with INDEX. So, I am just creating a small table here which is grocery and code.

Now what is the position of grocery in this range? I do not know about it. So, in that case as you know we are going to use MATCH. Lookup value will be grocery comma lookup array that in which column you need to find out, you need to match this grocery word.

So I am going to click on this and I am going to say control shift down arrow and then select it till the end. I will press F4 which is somehow not required because we need not to copy and paste it somewhere comma MATCH type will be zero. So I will close down the bracket now and when I press enter, see this.

It is coming at the third position from expenses. Same thing I need to find out that where this code word is coming in this complete heading. So, I am going to say equals to MATCH lookup value will be code comma lookup array will be from this cell control shift right arrow till the end comma zero.

Bracket close and enter. See this. It is telling us that it is coming at the second position.

So, grocery and code the position are there with us. Now we need to find out the value which is the code for grocery. This is what we need to do here.

So we have two matches over here and now we are going to apply index. So index is a very simple formula guys. If you know the column number like this and row number in that case you just need to assign the complete database and you need to put the row number as well as column number in index formula and it will give you the cross value out of that.

I will show you equals to index concentrate on the first syntax which is array with row number and column number. So array is the complete range which is this cell then control shift down arrow control shift right arrow. So, I will freeze it comma now it is asking for a row number through grocery we found out the row number.

The row number for the grocery is three comma column number is this two which column we are talking about that is a code column. So bracket close and enter. See this.

The code is coming which is RG S01 O2. So that is the grocery the code for the grocery is the same that we are getting the answer. I hope this is clear to you a bit and I would really suggest you do a lot of practice on this.

Now let me club this over here and then we can copy and paste it to what's right and down and we'll get all the values for this matrix. So I'm going to say equals to index array means the complete database that I have told you and I really want you to do one thing. I really suggest you to always select it from the very first cell that must be the heading.

So it should not confuse by the counting. So I'll select it by pressing control shift down arrow as well as control shift right arrow. I'll freeze it because I need to copy and paste it down by pressing F4 key on my keyboard function F4 key comma.

Now I do not know the row number. So I'm going to say I'm going to put match tab. Now look up value for this match.

Remember this. We are standing on row number right now. Just have a row number.

You cannot shuffle this because whatever the syntax is you have to follow that exactly like that. So I'm going to say first match and I'm going to find out the row number using this match. So look up value will be grocery comma look up array will be this expenses one because we know that the grocery home phone and all the things are here in this column and representing a particular row.

So that is where this is going to be our row number. So control shift down arrow and we'll select it till the end and I'm going to press F4 again comma zero then we'll close on the bracket for the first match and we'll get the row number now and as I told you earlier also we have a shortcut to see that what would be the value of this match so you can actually select it like this. So I've selected it and now if I press F9 function F9 key on my keyboard it will tell me the result which is three and you can see it as correct that what we will get.

Now I'll have to press control Z so that will show me the formula again now column number. So column number is basically the code right now code column. So for that again we have to put match tab now the lookup value for this match will be this word code comma lookup array will be this complete range from here till control shift right arrow till here.

So I'm going to freeze it by pressing F4 key on my keyboard comma zero. So first bracket will close down for match and the other one is for index. So we are done with our formula now.

So you can see one index and two matches in between. One thing we need to freeze in this B7 which is this cell as I have discussed earlier this kind of freezing and we look up and match video so you can refer that and here we are going to freeze or put a dollar sign just before B because I need to copy and paste it towards right and I don't want to change the color. And over here C6 I need to freeze the row number six.

So I'm going to put a dollar sign just before six. So when I copy and paste it towards down it should not change the row. So now if I press enter we are getting the result which is RGS0102 that's one.

And now when I copy it and I press shift and right arrow and down arrow and when I press enter you see that we are getting the results for all. So this is how it's a two way lookup. It is one of the most useful combination in lookup functionality.

I would really prefer that you should use index and match anywhere in your work wherever you are using VLOOKUP because somehow whatever VLOOKUP can do index and match can easily do as well as something which VLOOKUP cannot do can be done using index and match. So that is this is kind of an ultimate formula in lookup functionality. So I hope you enjoyed this video.

I again I'm saying that you need to do some practice. I know we are going ahead and it is kind of a complex function but it really needs some practice and you'll feel very much comfortable when you do it couple of times. That is why in this course I have created two exercise files for this particular function so that you can get a quick hands on on that.

So just open the next file which is exercise 0613 index and match and will do it and it has two part part one and part two. So it has two examples. So you have ample of exercises for you to do it.

Thank you.

Answers 8 Comments
Dislike Bookmark

Answered on 16 Mar Learn IT Courses/MS Office Software Training/Microsoft Excel Training/Excel Dashboard

Step 1-Select the table including the heading 2. Click on Insert Tab 3. Recommended Charts 4. All charts 5. And there you'll get all the different types of charts. Choose anyone like the Clustered column chart and hit OK. If you want to learn more, book a Demo class with me. Thanks. ...more

Step 1-Select the table including the heading 

2. Click on Insert Tab

3. Recommended Charts

4. All charts 

5. And there you'll get all the different types of charts. Choose anyone like the Clustered column chart and hit OK.

 

If you want to learn more, book a Demo class with me. Thanks.

Answers 4 Comments
Dislike Bookmark

Teaches

Microsoft Excel Training classes
1 Student

Class Location

Online Classes (Video Call via UrbanPro LIVE)

Student's Home

Tutor's Home

Years of Experience in Microsoft Excel Training classes

1

Teaches following Excel features

Advanced Excel, Basic Excel, Excel Macro Training

Teaching Experience in detail in Microsoft Excel Training classes

Master Microsoft Excel - Advanced.

 

What you'll learn...

1. Learn all about syntax, arguments and logic.

2. How to create custom and nested functions.

3. Learn the fastest and smartest ways of cleaning the raw data

4. Create dynamic reports by mastering one of the most popular tools, PivotTables.

5. Learn which chart/graph to use and when.

6. How to automate repetitive tasks in Excel using Macros.

7. Learn various 'must have' Excel shortcuts.

8. Learn how to manage heavy Excel files.

9. Amazing Bonus tricks that save tons of time and effort.

 

This Course will include

1. Live ZOOM Classes

2. Recordings of the Previous Classes (In case You Can't Attend LIVE Classes)

4. One Live QnA Session every Week to clear all your doubts

5. Downloadable Excel Files for Practice

6. Access on Laptop, mobile and TV

 

Course Duration - 29 days (can be customized accordingly)

Language - Hindi, English, Bengali (Any Language Preferred by Student)

 

Here is my Detailed Course Plan and Chapters

 

LOOKUP FUNCTIONALITY (BURN SIMPLE FIND TECHNIQUE)

 

Vlookup (Exact Match) # 1

Exercise Vlookup (Exact Match) # 1

Type of References (Use of $ Sign)

Vlookup (Exact Match) # 2

Exercise Vlookup (Exact Match) #2

Double Vlookup

Exercise Double Vlookup

Vlookup on Duplicate Values

Exercise Vlookup on Duplicate Values

Vlookup (Approximate Match)

Exercise Vlookup (Approximate Match)

Vlookup with IF (Conditional Vlookup)

Exercise Vlookup with IF (Conditional Vlookup)

Hlookup (Exact Match)

Exercise Hlookup (Exact Match)

Hlookup (Approximate Match)

Exercise Hlookup (Approximate Match)

Lookup (Don't Use This)

Exercise Lookup (Don't Use This)

Vlookup & Match (Create magical Vlookup)

Exercise Vlookup & Match ( Create a Magical Vlookup)

Match (Gives us Column & Row Number)

Exercise Match (Gives us Column & Row Number)

Index & Match (Made for each other)

Exercise number 1 of Index & Match (Made for each other)

Exercise number 2 of Index & Match (Made for each other)

Excel Files for Practice (Please Download)

 

DATE & TIME FUNCTION (A TRICKY GAME)

How Excel Records Date & Timer

Now, Today & Autofill

DateValue & TimeValue

Calculate WorkingDays

DatedIf

Excel Files for Practice (Please Download)

 

FINANCIAL FUNCTIONS (USEFUL FOR NON-FINANCE GUYS TOO)

 

Loan Calculation (PMT, PPMT, IPMT etc.)

Ex Loan Calculation (PMT, PPMT, IPMT etc.)

Creating a Loan Table

Ex Creating a Loan Table

Calculate Depreciation

Ex Calculate Depreciation

Excel Files for practice (Please Download)

 

SORT & FILTER (COMMON FOR ALL)

 

Basic Sorting & Shortcut keys

Ex Basic Sorting & Shortcut Keys

Sorting Using Custom List

Ex Sorting Using Custom List

Horizontal Sorting

Ex Horizontal Sorting

Basic Filter & Shortcut Keys

Ex Basic Filter & Shortcut Keys

Filter Problem # 1 (Copy Filtered Data without hidden rows)

Ex Filter Problem # 1 (Copy Filtered Data without hidden Rows)

Filter Problem # 2 (Pasting Values on Filtered Data)

Ex Filter Problem # 2 (Pasting Values on Filtered Data)

Filter Problem # 3 (Performing Calculation on Filtered Data)

Ex Filter Problem # 3 (Perform Calculations on Filtered Data)

Advanced Filter (Using Complex Criteria)

Ex Advanced Filter (Using Complex Criteria)

Excel Files for Practice (Please Download)

 

EXCEL CHARTS (ITS TIME FOR VISUAL PRESENTATION)

 

1. Excel Charts Introduction

2 How to Create a Chart

3 Link a Chart Title

4 How to Show Axis Title

5 How to Show Data Label

6 Column Chart

7 Bar Chart

8 Line Chart 1

9 Line Chart 2

10 Area Chart

11 Pie Chart

12 Pie of Pie or Bar of Pie

13 Line Chart with Log Scale

14 Bubble Chart

15 Selecting Chart Elements

16 Use of Format Dialog box

17 Modifying Chart & Plot Area

18 Modifying Data Series

19 How to handle Missing Data

20 Modifying 3-D Charts

21 Creating Chart Templates

22 Creating Picture Charts

23 Creating a Combination of upto 5 Different Charts

24 Show data with the chart in Data Table

25 Play with the Design & Layout of the Chart

Excel Files for Practice (Please Download)

 

DATA CONSOLIDATION (MULTIPLE WORKBOOKS WORKSHEETS)

 

Consolidate Data using Excel Inbuilt Function

Ex Consolidate data using Excel Inbuilt Function

Excel Files for Practice (Please Download)

 

DATA VALIDATION

 

Applying Data Validation

Ex Applying Data validation

Dependent Dropdown List

Ex Dependent Dropdown List

Using Data Validation with Vlookup & Match

Other Data Validation Examples

Excel Files for Practice (Please Download)

 

PROTECTION OF WORKSHEET WORKBOOK

 

Worksheet Level Protection

Ex Worksheet Level Protection

Cell Level Protection

Ex Cell Level Protection

Hiding Formulas from Formula Bar

Ex Hiding Formulas from Formula Bar

Protecting Workbook Structure

Ex Protecting Workbook Structure

Workbook Level Protection

Ex Workbook Level Protection

Excel Files for Practice (Please Download)

 

PIVOT TABLE & TECHNIQUES

 

Understanding pivot Table

Using Pivot Table in Real Scenario

Ex Using Pivot Table in Real Scenario

Grouping in Pivot Table

Ex Grouping in Pivot Table

Using Slicers (A Visually attractive Filter)

Ex Using Slicers (A Visually attractive Filter)

Excel Files for Practice (Please Download)

 

WHAT-IF ANALYSIS & SOLVER ADD-IN

 

Goal Seek (A Reverse Approach)

Ex Goal Seek (A Reverse Approach)

One-Way Data Table

Two-Way Data Table

Scenario Manager

Solver Add-In

Excel Files for Practice (Please Download)

 

16 AUTOMATION IN EXCEL (MACROS)

 

Record your first Macro

Record Another Macro

Assigning Macros to a Shape, Button, Picture

Editing or Deleting a Macro

Security Settings of a Macro

Excel Files for Practice (Please Download)

 

17. New Video Tutorials

 

*How to create a Map Chart in Excel

*How to use the Forecast sheet in Excel

*Excel Files for Practice (Please Download)

 

New Chapter - Conditional Formatting

 

*Highlight cells rules

*Top Bottom Rules

*Data Bars

*Color Scales

*Icon Sets

*Function based Conditional Formatting

*Exercise files for practice (please download)

 

Bonus Videos (In Hindi)

*Why my excel file size is huge and how to reduce Excel file size

*Problem of unwanted names when we create a copy of the sheet in Excel

*New chart types in Excel 2019/Office 365 || Map Chart || Funnel Chart

*How do you change the number format in Excel?

 

Types of Errors in Excel and How to resolve them

 

Excel Files for Practice (Please Download)

 

Other Experiences -

1. Taught, Motivated Students

2. Public Speaker

3. Conducted Webinars

4. Conducted Students Career Counseling

 

Let's discuss your specific needs and schedule a session at your convenience.

 

Best regards,

Md Khalid

5 out of 5 2 reviews

Mohammad Khalid
A

Microsoft Excel Training

"Teaching style of Khalid sir is very nice. I didn't even opened Ms office applications before I got his training. now I'm expert in Ms excel. He makes you understand complex functions in very simple way. "

Reply by Mohammad

Thanks for your Review.

Mohammad Khalid
J
Verified Student

Excel Masterclass: Beginner to Advanced

I loved

Audio/Video Quality

Teaching Method

Teacher's Knowledge

Have you attended any class with Mohammad?

Answers by Mohammad Khalid (2)

Answered on 23 May Learn IT Courses/MS Office Software Training/Microsoft Excel Training/Excel INDEX

let me explain you the data first. Here the data is that we have a serial number, we have the code for a particular expense and the expenses are given for the complete year. Jan, Feb, March and the quarter total are also given. Now, we have this small table in front of us in which we have a list of... ...more

let me explain you the data first. Here the data is that we have a serial number, we have the code for a particular expense and the expenses are given for the complete year. Jan, Feb, March and the quarter total are also given.

Now, we have this small table in front of us in which we have a list of expenses as well as for which particular column we need to find out the value for. First we need to find out the code for grocery, home phone, cable TV and tuition. Same we need to find out the expenses related to these expenses, these items in quarter 2, 3 and 4. If you see one thing that we are talking about code right now.

If you look at it, expenses are on column D and technically this is the third column in the data and code is the second column. So, this is basically a reverse pre-lookup or reverse lookup we are talking about. Now here, first I am going to show you that what INDEX do.

You know about the MATCH already. The MATCH function is that if I assign a MATCH function, just have a look. First I am going to break down the formula for you to make it easier for you to understand and then we will club it in a single cell.

So have a look. I am going to use MATCH first and then we will club it with INDEX. So, I am just creating a small table here which is grocery and code.

Now what is the position of grocery in this range? I do not know about it. So, in that case as you know we are going to use MATCH. Lookup value will be grocery comma lookup array that in which column you need to find out, you need to match this grocery word.

So I am going to click on this and I am going to say control shift down arrow and then select it till the end. I will press F4 which is somehow not required because we need not to copy and paste it somewhere comma MATCH type will be zero. So I will close down the bracket now and when I press enter, see this.

It is coming at the third position from expenses. Same thing I need to find out that where this code word is coming in this complete heading. So, I am going to say equals to MATCH lookup value will be code comma lookup array will be from this cell control shift right arrow till the end comma zero.

Bracket close and enter. See this. It is telling us that it is coming at the second position.

So, grocery and code the position are there with us. Now we need to find out the value which is the code for grocery. This is what we need to do here.

So we have two matches over here and now we are going to apply index. So index is a very simple formula guys. If you know the column number like this and row number in that case you just need to assign the complete database and you need to put the row number as well as column number in index formula and it will give you the cross value out of that.

I will show you equals to index concentrate on the first syntax which is array with row number and column number. So array is the complete range which is this cell then control shift down arrow control shift right arrow. So, I will freeze it comma now it is asking for a row number through grocery we found out the row number.

The row number for the grocery is three comma column number is this two which column we are talking about that is a code column. So bracket close and enter. See this.

The code is coming which is RG S01 O2. So that is the grocery the code for the grocery is the same that we are getting the answer. I hope this is clear to you a bit and I would really suggest you do a lot of practice on this.

Now let me club this over here and then we can copy and paste it to what's right and down and we'll get all the values for this matrix. So I'm going to say equals to index array means the complete database that I have told you and I really want you to do one thing. I really suggest you to always select it from the very first cell that must be the heading.

So it should not confuse by the counting. So I'll select it by pressing control shift down arrow as well as control shift right arrow. I'll freeze it because I need to copy and paste it down by pressing F4 key on my keyboard function F4 key comma.

Now I do not know the row number. So I'm going to say I'm going to put match tab. Now look up value for this match.

Remember this. We are standing on row number right now. Just have a row number.

You cannot shuffle this because whatever the syntax is you have to follow that exactly like that. So I'm going to say first match and I'm going to find out the row number using this match. So look up value will be grocery comma look up array will be this expenses one because we know that the grocery home phone and all the things are here in this column and representing a particular row.

So that is where this is going to be our row number. So control shift down arrow and we'll select it till the end and I'm going to press F4 again comma zero then we'll close on the bracket for the first match and we'll get the row number now and as I told you earlier also we have a shortcut to see that what would be the value of this match so you can actually select it like this. So I've selected it and now if I press F9 function F9 key on my keyboard it will tell me the result which is three and you can see it as correct that what we will get.

Now I'll have to press control Z so that will show me the formula again now column number. So column number is basically the code right now code column. So for that again we have to put match tab now the lookup value for this match will be this word code comma lookup array will be this complete range from here till control shift right arrow till here.

So I'm going to freeze it by pressing F4 key on my keyboard comma zero. So first bracket will close down for match and the other one is for index. So we are done with our formula now.

So you can see one index and two matches in between. One thing we need to freeze in this B7 which is this cell as I have discussed earlier this kind of freezing and we look up and match video so you can refer that and here we are going to freeze or put a dollar sign just before B because I need to copy and paste it towards right and I don't want to change the color. And over here C6 I need to freeze the row number six.

So I'm going to put a dollar sign just before six. So when I copy and paste it towards down it should not change the row. So now if I press enter we are getting the result which is RGS0102 that's one.

And now when I copy it and I press shift and right arrow and down arrow and when I press enter you see that we are getting the results for all. So this is how it's a two way lookup. It is one of the most useful combination in lookup functionality.

I would really prefer that you should use index and match anywhere in your work wherever you are using VLOOKUP because somehow whatever VLOOKUP can do index and match can easily do as well as something which VLOOKUP cannot do can be done using index and match. So that is this is kind of an ultimate formula in lookup functionality. So I hope you enjoyed this video.

I again I'm saying that you need to do some practice. I know we are going ahead and it is kind of a complex function but it really needs some practice and you'll feel very much comfortable when you do it couple of times. That is why in this course I have created two exercise files for this particular function so that you can get a quick hands on on that.

So just open the next file which is exercise 0613 index and match and will do it and it has two part part one and part two. So it has two examples. So you have ample of exercises for you to do it.

Thank you.

Answers 8 Comments
Dislike Bookmark

Answered on 16 Mar Learn IT Courses/MS Office Software Training/Microsoft Excel Training/Excel Dashboard

Step 1-Select the table including the heading 2. Click on Insert Tab 3. Recommended Charts 4. All charts 5. And there you'll get all the different types of charts. Choose anyone like the Clustered column chart and hit OK. If you want to learn more, book a Demo class with me. Thanks. ...more

Step 1-Select the table including the heading 

2. Click on Insert Tab

3. Recommended Charts

4. All charts 

5. And there you'll get all the different types of charts. Choose anyone like the Clustered column chart and hit OK.

 

If you want to learn more, book a Demo class with me. Thanks.

Answers 4 Comments
Dislike Bookmark

Mohammad Khalid describes himself as Personal and Corporate Excel Trainer | Trained +500 Professionals Worldwide. He conducts classes in Microsoft Excel Training. Mohammad is located in Santipara Rd Railway Colony, Rampurhat. Mohammad takes Online Classes- via online medium. He has 1 years of teaching experience . Mohammad has completed Bachelor of Arts (B.A.) from Burdwan University in 2020 and Post Graduation Diploma in Mass Communication and Journalism from Indira Gandhi National Open University in 2023. He is well versed in English, Hindi, Bengali and Urdu. Mohammad has got 2 reviews till now with 100% positive feedback.

X
X

Post your Learning Need

Let us shortlist and give the best tutors and institutes.

or

Send Enquiry to Mohammad

Let Mohammad know you are interested in their class

Reply to 's review

Enter your reply*

1500/1500

Please enter your reply

Your reply should contain a minimum of 10 characters

Your reply has been successfully submitted.

This website uses cookies

We use cookies to improve user experience. Choose what cookies you allow us to use. You can read more about our Cookie Policy in our Privacy Policy

Accept All
Decline All

UrbanPro.com is India's largest network of most trusted tutors and institutes. Over 55 lakh students rely on UrbanPro.com, to fulfill their learning requirements across 1,000+ categories. Using UrbanPro.com, parents, and students can compare multiple Tutors and Institutes and choose the one that best suits their requirements. More than 7.5 lakh verified Tutors and Institutes are helping millions of students every day and growing their tutoring business on UrbanPro.com. Whether you are looking for a tutor to learn mathematics, a German language trainer to brush up your German language skills or an institute to upgrade your IT skills, we have got the best selection of Tutors and Training Institutes for you. Read more