All articles with 'vlookup' Tag
This is part of our Excel Interview Questions series.
VLOOKUP or INDEX+MATCH? When you should use each function and why?
This is such a great question to ask in interviews. So in my first installment of Excel interview questions, let me answer it.Continue »
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.Continue »
Excel has hundreds of formulas. But as a new learner or user, you may want to just focus on top 10 formulas to get the most out of it. Assuming you already know the basics (check out Beginner Excel page if you are complete newbie), here is a list of top 10 Excel formulas for you.Continue »
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.Continue »
Would you like to spend next 5 minutes learning how to create an mutual fund tracker excel sheet?
Make a live, updatable mutual fund portfolio tracker for Indian markets to keep track of your investments using this example.Continue »
Call them by any name – Budget vs. Actual, Target vs. Actual, Goal vs. Progress, KPIs, Performance charts, but they are the bread and butter of business charting. So how about a drop dead gorgeous and insightful chart for your next meeting with the folks upstairs? Something like above.
Read on to learn how to create this chart in Excel.Continue »
VLOOKUP may not make you tall, rich and famous, but learning it can certainly give you wings. It makes you to connect two different tabular lists and saves a ton of time. In my opinion understanding VLOOKUP, INDEX and MATCH worksheet formulas can transform you from normal excel user to a data processing beast. Today, […]Continue »
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 »
Here is an interesting problem to keep your brain cells fight boredom on this Friday & weekend.
Let’s say you have some data like above.
And you want to know, for a given customer name (in cell G4),
- What is the most frequent quantity?
- What is the most often purchased item?
How would you write formulas to get these answers?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 »
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 »
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 »
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),
- Lack of good quality mangoes to eat
- Intense heat and humidity
- 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 »
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 wildcardContinue »