Find the best tutors and institutes for MS Office Software Training

Find Best MS Office Software Training

Please select a Category.

Please select a Locality.

No matching category found.

No matching Locality found.

Outside India?

Search for topics

MS Office Software Training Updates

Ask a Question

Post a Lesson

All

All

Lessons

Discussion

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

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 MS Office Software Training

Find best MS Office Software Training 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 29 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

Ashwin George

Pivot Tables and Pivot Charts are easy tools to make sense of large datasets. If you are willing to invest more time to learn formulas, they can be used to refine and analyse your data.
Answers 3 Comments 1
Dislike Bookmark

Answered on 21 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

Which is the best Microsoft advanced excel training institute in Delhi?

S P Sharma

Tutor

S P SHARMA CLASSES http://www.spsharmag.com/
Answers 1 Comments
Dislike Bookmark

Looking for MS Office Software Training

Find best MS Office Software Training in your locality on UrbanPro.

FIND NOW

Answered on 30 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

Which is the best Excel course training provider in India?

S M Ejaz

Tutor

You can contact me on my mobile or whatsapp seven four zero six six eight three three zero six . I am MS-Excel trainer. i provide on-line and home tuition by reaching students home for MS-Excel course.
Answers 2 Comments 1
Dislike Bookmark

Looking for MS Office Software Training

Find best MS Office Software Training in your locality on UrbanPro.

FIND NOW

About UrbanPro

UrbanPro.com helps you to connect with the best MS Office Software Training in India. Post Your Requirement today and get connected.

Overview

Questions 218

Lessons 161

Total Shares  

+ Follow 15,652 Followers

Related Topics

Top Contributors

Connect with Expert Tutors & Institutes for MS Office Software Training

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