UrbanPro
true

Learn Microsoft Excel Training from the Best Tutors

  • Affordable fees
  • 1-1 or Group class
  • Flexible Timings
  • Verified Tutors

How Lookup function to replace Nested If statement

Kaustubh Chavan
11/09/2018 0 0

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.

 

0 Dislike
Follow 1

Please Enter a comment

Submit

Other Lessons for You

Data Analysis with MS Excel Filter - Top Performers, Bottom Performers, Above Average Performers, Below Average Performers!
In the following, You would find lesser-known, unexplored yet Powerful Features of Filter. Please refer to sample dataset below: To do Data Analysis:Step 1) Apply Filter on the dataset.Step 2) In Sales...

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

An Introduction to Business Intelligence Concepts
Looking for a Business Intelligence (BI) solution for your company can be intimidating. BI uses its own special terminology and the database design concepts can be difficult to grasp. So where do you...

Hide Duplicate Values & Apply Borders Using Conditional Formatting
Please refer following screenshots:1st Screenshot: Original Dataset2nd Screenshot: After Conditioinal Formatting is applied In 2nd screenshot, using Conditional Formatting:1) Repeat months are...

Importance of dash board in Business/org
A business intelligence (BI) dashboard is a data visualization tool that consolidates and displays the current status of metrics and key performance indicators (KPIs) for an organization, department or...
X

Looking for Microsoft Excel Training Classes?

The best tutors for Microsoft Excel Training Classes are on UrbanPro

  • Select the best Tutor
  • Book & Attend a Free Demo
  • Pay and start Learning

Learn Microsoft Excel Training with the Best Tutors

The best Tutors for Microsoft Excel Training Classes are on UrbanPro

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