VLOOKUP multiple matches – trick
We all know that VLOOKUP can find first match and return the results. But what if you want all the matches? Use this simple trick instead.
Lenient lookup [Advanced Formula Trick]
We all know VLOOKUP (or INDEX+MATCH) as an indispensable tool in our Excel toolbox. But what if you want the lookups to be a little gentler, nicer and relaxed?
Let’s say you want to lookup the amount $330.50 against a list of payments. There is no exact match, but if we look 50 cents in either direction, then we can find a match. Here is a demo of what I mean.
Unfortunately, you can’t convince VLOOKUP to act nice.
Hey VLOOKUP, I know you are awesome and all, but can you cut me some slack here?
VLOOKUP is tough, reliable and has a cold heart. Or is it?
In this post, let’s learn how to do lenient lookups.
Create your first interactive chart in Excel with this tutorial
Ever wanted to make a cool, snazzy interactive chart in Excel? Something like this:
In this tutorial, learn all about making your very first interactive chart. We use both formulas and pivot tables to build two versions of an awesome interactive chart in Excel.
VLOOKUP that fat table with ease [3 quick tips]
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.
Awesome chart to visualize Salary Increases for 3,500+ people [Tutorial]
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.
Designing awesome financial metrics dashboard [tutorial]
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.
An odd lookup problem [Formulas]
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?