All articles with 'MATCH()' Tag
Time for some good, old fashioned VLOOKUP love. Let’s say you are writing VLOOKUP()s to get data from an unusually fat table, ie one with heaps of columns. You want to get to lookup ID in first column and get thingamajig in what is that column number. Well, better get counting from 1 and after 19 seconds and lots of squinting you arrive at column number 53 – which has thingamajig.
If this sounds like your VLOOKUP routine, check out these three amazingly simple tips to save some time and effort with your lookups.Continue »
Game for some charting awesomeness?
Off late, I have been doing a lot of data analysis and visualization on performance ratings, salary hike, gender pay equality etc. Today let me share you an awesome way to visualize massive amounts of data.
Scenario: Your organization of 3,686 people recently went thru annual performance ratings & review process. At the end of it, everyone was offered some salary increase (from $0 to $24,000 per year). You have 7 business groups. How do you tell the story of all these salary hikes in one chart?
How about the one above?
Ready to know how to create this in Excel? Read on.Continue »
In this amazing guest post, the winner of our 2016 dashboard contest – Chandeep – Explains how he constructed the jaw dropping beauty (shown above) using Excel, creativity, love and sweat. Grab a full cup of coffee (or whatever liquid fancies you) and read on. Take lots of notes and play with the ideas in Excel while reading to maximize your learning.
Thanks Chandeep.Continue »
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 »
Pivot tables are lovely. But sometimes they are hard to work with. Let’s say you are analyzing some HR data and want to see number of weeks worked in each hour classification.
And you want this.
Except, there is a teeny tiny problem.
The sort order on the classification is all messed up.
Here is a quick fix to get custom sort order on your pivot table row labels.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 »
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 »
Lets look at how to apportion sales according to multiple criteriaContinue »
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 »
Learn how to find which worksheet a max or min value occurs on using this neat formulaContinue »
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 »
Who is the most consistent of all?
Imagine you are a category manager at a large e-commerce company. Your site offers various products, but you don’t really make these products. You list products made by other vendors on your site. Every day, these vendors would send you invoices for the amount of product they have sold. Above is a snapshot of such invoices.
Looking at this list, you have a few questions.
- Who is the best seller?
- Who is the most active seller?
- Who is the most consistent seller?
- Which seller has fewest invoices?
Let’s go ahead and answer these using Excel. Shall we?Continue »