All articles with 'INDEX()' Tag
Let’s say you have some employee data in employee name, manager name format. But the data is all in one column, with odd rows containing employee names & even rows containing manager names. Something like above.
And you want to find out who is the boss for a given employee. Say, “Andrea Nichols”.
Your regular MATCH() formula for Andrea over the data range returns wrong answer as it will find first occurrence of Andrea (which in this case happens to be on even row, hence a manager record).
So how would you write the lookup formula?Continue »
First a quick personal update: There has been a magnitude 7.8 earth quake in NZ on 14th November 2016 early morning. It is centered in Kaikoura, which is about 250 km away from Wellington. We did feel several shakes and after shocks. It has been an interesting and often scary experience. But my family is safe. I feel very sad for the all the damage and the loss for families in NZ. If you suffered from this quake, My prayers and thoughts are with you.
Yesterday, a friend asked me an interesting question. He has school distance data, like above. He wants to know which is the closest school for each school.
There are a few ways to answer this question. Let’s examine two approaches – formulas & pivot tables and see the merits of both.Continue »
Ladies & gentlemen, its time we revived the much loved Chandoo.org podcast. In the 55th episode, I do a lousy imitation of Arnold Schwarzenegger’s famous “I will be back” and tell you why there was such a long gap between episodes, my plans for reviving our podcast and more.
What is in this session?
In this podcast,
- Why there was such a long gap between last and this episode
- What next?
- How to extract every 6th item from a list?
Over the years, we have discussed a whole heap of techniques to visualize budget vs. actual charts. Today let’s take a ride on this slope again and learn another fun, silly & awesome way to depict target vs. actual progress.
Introducing biker on a hill chart
Biker on a hill!?! Don’t worry, I didn’t fall down on a descent and lose my brain. I am talking about an Excel chart to visualize target vs. actual progress on a time line with biker on a hill analogy. See the above chart, you will know.
Looks interesting? Read on to learn how to create this in Excel.Continue »
Learn how to convert a Roman Numeral to a Number using this nifty formula. No VBA required.Continue »
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 »
Recently I saw an interesting Earth Venus cosmic dance video on Facebook. See the original video below or here.
Although this is not entirely accurate from physics & astronomy perspectives, the dance is a stunning example of patterns that are generated by simple things.
I wanted to recreate this cosmic dance in Excel. How else am I to get my spreadsheet fix on a Saturday?
Here is a quick demo of the final outcome. Read on to learn more about the Earth Venus cosmic dance.Continue »
This is part two of our three part series on how to analyze half a million customer complaints. Read part 1 here.
Analyzing Regional Trends
As introduced in part 1, our complaints dataset has geographical information too. We know the state & zip code for each complaint. Please note that zip codes are partial or missing for a 10% of the data.
In this article, let’s explore three ways to analyze regional trends.
- Regional trends by state, product & issue
- Complaints per million by state
- Complaints by zip code
Lets look at how to apportion sales according to multiple criteriaContinue »
It seems spreadsheets & dinosaurs on a collision course. How else can you explain Jon’s XKCD Velociraptor problem solved with Excel and now this. Debby, alert reader of our blog sent me this email yesterday.
I need an algebraic formula to solve this in Excel
I have 5 heads, 5 bodies, 4 arm sets, 4 leg sets and 3 tails. I need to see if I can create 1000 dinosaurs from these, and if that’s too many AND I need the 5 digit groupings to prove it and create them.
basically Xa*Xb*Xc*Xd*Xe=1000 – I’m not supposed to go over 1200. […] And then I want the 5 digit combinations if possible – right now they are trying to do the combinations by hand – would be awesome if we could do it in Excel.
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
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 »
Imagine you are the head of training department at ACME Inc. You arrange training programs round the year to empower your team. It is hard work, coordinating between employees, trainers, department heads, venues and coffee machines. What if there is something to help you keep track of all this? I am not talking about getting you a shiny new iPad, you silly. I am talking about a tracker & calendar built in Excel that ties everything together (well, almost everything, you still have to fill the coffee machine.)
We are going to build a training program tracker & calendar using Excel.Continue »
Find the Cell Address for a value from a 2D or 3D Range
(2D & 3D Reverse Lookup)
Learn how to find which worksheet a max or min value occurs on using this neat formulaContinue »