fbpx

All articles with 'vlookup' Tag

Analyzing 300,000 calls for help [case study]

Published on Aug 24, 2016 in Analytics, Learn Excel, Pivot Tables & Charts, Power Pivot
Analyzing 300,000 calls for help [case study]

Over the weekend, I got an email from Mr. E, one of my students. Mr. E works at a police department in California and as part of his work, he was looking at calls received by police. Whenever police get a call for help, multiple teams can respond to the call and go to the location. All of these dispatches are recorded. So a single call can have several such dispatches. And Mr. E wanted to findout which team responded the first. The problem?

Finding the first responded team is tricky.

Today let’s take up this problem as a case study and understand various methods to solve it.  We are going to learn about writing better lookups, pivot tables, power pivot and optimization. Put on your helmets, cause this is going to be mind blowingly awesome.

Continue »

Generating sequence numbers from cluster values [VLOOKUP to the rescue]

Published on Jun 2, 2016 in Excel Howtos
Generating sequence numbers from cluster values [VLOOKUP to the rescue]

Last night I got an email from Joshua, one of our readers with the subject – Hard Excel problem. Hard?!?, at this stage of summer, the hard problems seem to be (in no particular order),

  1. Lack of good quality mangoes to eat
  2. Intense heat and humidity
  3. Lack of good quality mangoes to eat

Yes, I like mangoes.

Any how, back to Joshua’s email, So I got curios and read it. He is facing a curious problem.

Continue »

Not so wild lookups [video]

Published on Feb 12, 2016 in Excel Howtos, Learn Excel
Not so wild lookups [video]

In case, this is the first time you are hearing about Excel formula wildcards, check out the Using wildcards in Excel VLOOKUP formula tutorial.

So you know about wild cards like * ?, now how would you tell VLOOKUP to ignore them?

Say, you are genuinely interested in looking the value “* Payroll” in a lookup table. What then?

This is exactly the problem faced by Peter in our forum post VLOOKUP and cells with “*” NOT to be interpreted as wildcard

Continue »

CP051: VLOOKUP FAQs – Most frequently asked questions about VLOOKUP – Answered

Published on Jan 7, 2016 in Chandoo.org Podcast Sessions
CP051: VLOOKUP FAQs – Most frequently asked questions about VLOOKUP – Answered

In the 51st session of Chandoo.org podcast, let’s discuss most frequently asked questions about VLOOKUP.

What is in this session?

In this podcast,

  • What is VLOOKUP?
  • What happens when VLOOKUP can’t find the value?
  • Should my list be sorted?
  • Is VLOOKUP slower than INDEX + MATCH?
  • What if my list has multiple matches?
  • How to fetch 2nd / 3rd matching item?
  • How to fetch all matching items?
  • How to fetch items matching multiple conditions?
  • How to speed up VLOOKUP?
  • Why doesn’t my VLOOKUP work?
  • What to do in case of errors?
  • Resources for you
Continue »

Pricing Tier Lookup formula

Published on Dec 1, 2015 in Excel Howtos, Learn Excel
Pricing Tier Lookup formula

Here is an interesting twist on the good old VLOOKUP. How to find the pricing applicable for given quantity of a product?

Something like above.

Looks interesting? Then read on…

Continue »

Case Sensitive Lookups

Published on Sep 7, 2015 in Excel Howtos
Case Sensitive Lookups

We all know that VLOOKUP (and its cousins MATCH, HLOOKUP and LOOKUP) are great for finding information you want. But they are helpless when you want to do a case-sensitive lookup.

So how do we write case sensitive VLOOKUP formulas?

Simple. We can use EXACT formula.

Continue »

Monthly Planner Template [downloads]

Published on Aug 28, 2015 in Templates
Monthly Planner Template [downloads]

Here is an awesome planner template to help you manage activities over a month. It is useful for charity drives, activity planning, school schedules, marketing initiatives, project planning etc.

Read on to download a copy of the template & learn how to use it.

Continue »

VLOOKUP the last value

Published on Aug 11, 2015 in Excel Howtos, Learn Excel
VLOOKUP the last value

VLOOKUP is one of the most useful Excel functions. So much so that I even wrote a book about it. But it has one serious limitation.

It looks up the first occurrence and returns corresponding data.

What if you want to find the last value?

Say, for example, you are looking at a task assignment list and want to know what is the last task assigned to employee Emp13?

We want to extract the task “Make amazing workbook”. Of course our good old VLOOKUP stops once it finds Emp13 and returns the answer as “Create intuitive workbook”.

Continue »

Narrating the story of change using Excel charts – case study

market-share-changes-over-time

Here are three questions you often hear from your boss:

  1. What changes are happening in our business and how do they look?
  2. Do you know how to operate this new coffee machine?
  3. Why does every list has 3 items?

Jokes aside, our urge to find change in environment predates cave drawing, slice bread and Tommy Lee Jones. So, today let’s examine a very effective chart that tells the story of change and re-create it in Excel.

Continue »

Excel to the Next Level by Mastering Multiple Occurrences

Published on Dec 9, 2014 in Learn Excel
Excel to the Next Level by Mastering Multiple Occurrences

This is a guest post by Sohail Anwar.

August 29, 1994. A day that changed my life forever. Football World Cup? Russia and China de-targeting nuclear weapons against each other? Anniversary of the Woodstock festival?

No, much bigger: Two Undertakers show up at WWE Summerslam for an epic battle. Needless to say: MIND() = BLOWN().

And thus begun one boy’s journey into understanding the phenomenon of Multiple Occurrences.

My journey continued, when just a few years later my grandfather handed me down a precious family heirloom: A few columns of meaningless data that I could take away and analyze in Excel. You may laugh but in the 90’s, every boy only wanted two things 1) Lists of pointless data and …

Continue »

Looking up when the data won’t co-operate (case study)

Published on Nov 4, 2014 in Excel Challenges, Excel Howtos
Looking up when the data won’t co-operate (case study)

Occasionally we deal with data that is so uncooperative that we might as well give up and go back to calculators & ledger books.

Recently I found myself in such a situation and learned something new.

Introducing … data that won’t play nice

Drum roll please. Here is a data-set that I got from somewhere.

The problem – build a lookup formula

And the problem. Oh, simple. Write a lookup formula to find how many customer walk-ins we have on any given day.

But how?

Continue »

The ultimate VLOOKUP trick – Multi-condition Lookup

Published on Oct 28, 2014 in Learn Excel
The ultimate VLOOKUP trick – Multi-condition Lookup

This is a guest post by Sohail Anwar.

Let’s not bore you with an intro. You are about to learn a VLOOKUP trick that Lucifer himself would not want you to know. It’s so absurdly powerful that it was developed in a lab and had to be tested on Rocky’s arch nemesis Ivan Drago.

Presenting the Multiple criteria VLOOKUP!

…boring…pass, we’ve seen it.

Oh, have you? Not like this you haven’t. This will change the way you work with Excel.

Let me start with an easy example. Here’s some data and we would love to know what Bb and Dd is.

Continue »

Charmed Price Problem

Published on Oct 6, 2014 in Excel Howtos
Charmed Price Problem

Here is a charming little problem to kick start your day.

Lets say you run a cute little bakery around the corner. Since you want your prices to look charming, you have a policy to round them down or up based on below rule.

If the price ends with 0, 1 or 2 cents, round it down to 9 cents.

If the price ends with 3, 4 or 5 cents, round it up to 5 cents.

If the price ends with 6, 7, 8 or 9 cents, round it up to 9 cents.

For example,

So how do you round to nearest charmed price? You could do it manually. But you would rather bake a few more of those Tiny Cup Cakes than waste time rounding the prices. So you want an automatic way to round prices. This is where Excel helps.

Continue »

CP021: How to quickly compare 2 lists in Excel

Published on Oct 2, 2014 in Chandoo.org Podcast Sessions, Excel Howtos
CP021: How to quickly compare 2 lists in Excel

In the 21st session of Chandoo.org podcast, lets compare lists. Quickly

What is in this session?

Comparing things is a favorite pastime for analysts all over the world. Sadly, it is also an area where we waste hours. So in this episode, I share my top secret comparison techniques to save you time.

Note: This is a short format podcast. That means you spend less time listening to it, while becoming more awesome.

In this podcast, you will learn,

  • Why I sound like I am on a secret mission at a mafia hideout.
  • 5 ways to compare 2 lists
    • Manual method
    • Conditional Formatting
    • Row Differences
    • LOOKUP formulas
    • COUNTIF formulas
  • Bonus tip: Removing duplicates
  • Conclusions
Continue »

CP008: 6 Tips to handle workbooks made by someone else, #4 is something I struggle with too!

Published on May 15, 2014 in Chandoo.org Podcast Sessions
CP008: 6 Tips to handle workbooks made by someone else, #4 is something I struggle with too!

Here is a problem we all face once in a while. We inherit this bulky, bloated, leaking at the edges workbook from a colleague. Now the onus of maintaining it is on us. The person who made this workbook is nowhere to be found. May be she is vacationing in Hawaii sipping pineapple juice. May be he became a vice president and roaming the country in your company’s private jet.

So what do we do? How do we handle this inheritance?

That is the topic of our podcast, episode 8.

In this podcast, you will learn,

  • An overview of the inheritance problem
  • 6 Tips to understand workbooks made by someone else
  • Tip 0: Talk to the creator
  • Tip 1: Model the workbook on paper
  • Tip 2: Locate the engine, ie the formulas
  • Tip 3: See what else is under the hood – hidden sheets, names, VBA code
  • Tip 4: Annotate (add comments) as you learn
  • Tip 5: Locate the controls – inputs, assumptions, scenarios
  • Tip 6: Re-construct from scratch
  • Deep dive in to understanding the formulas
  • Deep dive in to understanding VBA code
  • Conclusions
Continue »