Find the best tutors and institutes for Microsoft Excel Training

Find Best Microsoft Excel Training classes

Please select a Category.

Please select a Locality.

No matching category found.

No matching Locality found.

Outside India?

Microsoft Excel Training Updates

Ask a Question

Post a Lesson

All

All

Lessons

Discussion

Lesson Posted on 22 Oct IT Courses/MS Office Software Training/Microsoft Excel Training/Advanced Excel IT Courses/MS Office Software Training/Microsoft Excel Training IT Courses/MS Office Software Training/Microsoft Excel Training/Excel VBA & Macro Training

Practical use of Two way Look-up (Category: Advanced Excel)

Rakesh Pradhan

I have done BTech and MTech in CS from VTU Bangalore. I have around 3 years of Experience in Teaching....

///////////////////////////////////////////////////////////////
Comments
Dislike Bookmark

Answered on 07 Dec IT Courses/MS Office Software Training/Microsoft Excel Training/Advanced Excel

Eagle Wings Training Centre

Trainer

Data validation is to validate any particular cell or selected range of cells or entire workbook where we can validate any specific value in cell snd user will not allow to edit or add any value besides from the data that we have validated...
Answers 5 Comments
Dislike Bookmark

Lesson Posted on 11 Sep IT Courses/MS Office Software Training/Microsoft Excel Training IT Courses/MS Office Software Training/Microsoft Excel Training/Advanced Excel

How Lookup function to replace Nested If statement

Kaustubh Chavan

-Experience of teaching. -Experience of handling multiple students at time. -Working in a MIS profile...

Nested If statements are complicated to type down and include many parameters. The best method to replace them is to use the lookup function. Using the lookup function shortens the formula at a considerable level. Let's take an example of a lookup function's approach to solve the problem of Nested If... read more

Nested If statements are complicated to type down and include many parameters. The best method to replace them is to use the lookup function. Using the lookup function shortens the formula at a considerable level.

Let's take an example of a lookup function's approach to solve the problem of Nested If statement.

Suppose I want to calculate commission on sales for a salesman.The % of the commission is tabulated in ascending order of the sales amount, that means. The % of the commission will increase as the sales amount rises.

For the demonstration purpose. I have designed the following commission table.

From To %
0 79999 0
80000 100000 0.5
100001 149999 0.6
150000 199999 0.65
200000 249999 0.7
250000 299999 0.75
300000 349999 0.8
350000 399999 0.9
400000 999999999999 0.95

 

According to the above table. If sales figure is from 80000 to 100000, the salesperson will get 0.5% of commission on the sales amount, and the person who has credited a whopping sales amount of 400000 and above will get the commission of 0.95%

This table has multiple conditions so naturally Nested if formula comes in mind where we need to test various states using various if statement. Let us study the use of Nested If statement

 

 

As you can see the nested if statement becomes pretty lengthy and cumbersome to write.

 

So let solve the above issue using Lookup function.

As you can see in the above image, we have used the lookup function. The sales person Tom has credited a sales amount of Rs.272840/-.In our table, the amount falls in the range of 250000 to 299999 and % against this range is 0.75.

So here is the syntax of LOOKUP function.

1)LOOKUP( value, lookup_range, [result_range] )
2)LOOKUP( value, array )


We will take the second formula.

Our Lookup table that is the array part of the formula will be taken by commission table ranging from F2 TO H10 and will be locked by $ Dollar Sign, you can see in the above image.


While the value part of the formula will be taken by the sales achieved by the salesperson that is cell B2

After inserting the formula, we get the outcome.

Just drag the formula down.

This way we can replace Nested If statement with Lookup function.

 

read less
Comments
Dislike Bookmark

Looking for Microsoft Excel Training classes

Find best Microsoft Excel Training classes in your locality on UrbanPro.

FIND NOW

Answered on 20 Aug IT Courses/MS Office Software Training/Microsoft Excel Training IT Courses/MS Office Software Training/Microsoft Excel Training/Basic Excel IT Courses/MS Office Software Training/Microsoft Excel Training/Advanced Excel

Ramesh KN

Corporate Trainer

Though not mandatry, it still continues as one of the best option
Answers 3 Comments
Dislike Bookmark

Answered on 20 Aug IT Courses/MS Office Software Training/Microsoft Excel Training IT Courses/MS Office Software Training/Microsoft Excel Training/Basic Excel IT Courses/MS Office Software Training/Microsoft Excel Training/Advanced Excel

Tarun Kant Gautam

There is no need of mathematics for learn excel you just have good understanding of logic n lit bit knowledge of logical mathematics
Answers 3 Comments
Dislike Bookmark

Answered on 28 Jul IT Courses/MS Office Software Training/Microsoft Excel Training IT Courses/MS Office Software Training/Microsoft Excel Training/Basic Excel IT Courses/MS Office Software Training/Microsoft Excel Training/Advanced Excel

Satyaprakash Tripathy

MS excel is a vast course and learning the MS excel never end. So it is always better to learn the abc of Excel by any source i.e. from Institure or from any online tutorial etc. Next come to adavance excel learning, for this you first know your requiremnt from an excel expert and then go for learning... read more

MS excel is a vast course and learning the MS excel never end. So it is always better to learn the abc of Excel by any source i.e. from Institure or from any online tutorial etc. Next come to adavance excel learning, for this you first know your requiremnt from an excel expert and then go for learning to any mode of tutorial, who can committee you to fulfill your requiremnet.

read less
Answers 3 Comments
Dislike Bookmark

Looking for Microsoft Excel Training classes

Find best Microsoft Excel Training classes in your locality on UrbanPro.

FIND NOW

Answered on 04 Jul IT Courses/MS Office Software Training/Microsoft Excel Training IT Courses/MS Office Software Training/Microsoft Excel Training/Basic Excel IT Courses/MS Office Software Training/Microsoft Excel Training/Advanced Excel

Kishore T.

Data Scientist

The most important things are various functions like vlookp hloop sumif countif sumifs countifs rank match index len mid month day pivot tables and excel graphs etc.
Answers 3 Comments
Dislike Bookmark

Answered on 17 Aug IT Courses/MS Office Software Training/Microsoft Excel Training IT Courses/MS Office Software Training/Microsoft Excel Training/Basic Excel IT Courses/MS Office Software Training/Microsoft Excel Training/Advanced Excel

S M Ejaz

Tutor

Hi Kartika It is always recommended that you take help from a subject matter expert who is good in MS-Excel, you have lot of mentors who can provide you training in Urbanpro. They can teach you either on-line, or by directly reaching you at your conveneant time and place as you wish. Thanks and... read more

Hi Kartika

 

It is always recommended that you take help from a subject matter expert who is good in MS-Excel, you have lot of mentors who can provide you training in Urbanpro. They can teach you either on-line, or by directly reaching you at your conveneant time and place as you wish.

 

Thanks and Regards

 

Ejaz

read less
Answers 3 Comments
Dislike Bookmark

Answered on 29 Jun IT Courses/MS Office Software Training/Microsoft Excel Training IT Courses/MS Office Software Training/Microsoft Excel Training/Basic Excel IT Courses/MS Office Software Training/Microsoft Excel Training/Advanced Excel

Rajan Ghadi

Trainer

Microsoft website would give you many videos to learn. You decide which area you understand as Advance.
Answers 3 Comments
Dislike Bookmark

Looking for Microsoft Excel Training classes

Find best Microsoft Excel Training classes in your locality on UrbanPro.

FIND NOW

Answered on 07 Aug IT Courses/MS Office Software Training/Microsoft Excel Training IT Courses/MS Office Software Training/Microsoft Excel Training/Basic Excel IT Courses/MS Office Software Training/Microsoft Excel Training/Advanced Excel

Lokanatha

it would be better if u share the templet, which you will be able to understand easily.
Answers 3 Comments
Dislike Bookmark

About UrbanPro

UrbanPro.com helps you to connect with the best Microsoft Excel Training classes in India. Post Your Requirement today and get connected.

Overview

Questions 95

Lessons 45

Total Shares  

+ Follow 44,025 Followers

Related Topics

Top Contributors

Connect with Expert Tutors & Institutes for Advanced Excel

x

Ask a Question

Please enter your Question

Please select a Tag

UrbanPro.com is India's largest network of most trusted tutors and institutes. Over 25 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 6.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