Search

All articles with 'MATCH()' Tag

What is XLOOKUP and how to use it in Excel?

Published on Mar 12, 2024 in Excel Howtos

Think of XLOOKUP as an improved version of VLOOKUP. In this article, learn all about the XLOOKUP function, it’s syntax, parameters with real-world xlookup examples.

Continue »

VLOOKUP(), MATCH() and INDEX() – explained in plain English

Published on Feb 11, 2024 in Featured, Learn Excel

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 »

Range Lookup in Excel – How to lookup the pricing tier? [Formulas]

Published on Dec 17, 2022 in Learn Excel

Excel formula to get a match from a given value in excel when you have start and end values in lookup table. This technique is useful for looking up a matching price from a pricing tier table or date in a range of start and end dates.

Continue »

Write a formula to get Department Budget for a Month [Homework]

Published on Mar 12, 2021 in Excel Challenges

Time for another homework. You got a spreadsheet of department budgets and need to write a formula to get budget for a given department, month combination.

Continue »

Easy Website Metrics Dashboard with Excel

Published on Apr 28, 2020 in Charts and Graphs, Learn Excel, Templates

Do you run an e-commerce website? You are going to love this simple, clear and easy website metrics dashboard. You can track 15 metrics (KPIs) and visualize their performance. The best part, it takes no more than 15 minutes to setup and use. Here is a preview of the dashboard.

Continue »

Job Title Matching Problem [Excel Homework]

Published on Aug 30, 2019 in Excel Challenges, Power Query

Howdy folks. Almost the end of August here. Let’s wrap it up with a nice little challenge, inspired from my recent consulting gig. Say you are looking at few job titles that look similar and want to match them to correct title.

Continue »

VLOOKUP or INDEX+MATCH? – Excel Interview Question – 01

Published on Aug 15, 2019 in Interview Questions, Learn Excel

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 »

VLOOKUP multiple matches – trick

Published on Apr 16, 2019 in Excel Howtos, Learn Excel

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 »

Published on Sep 13, 2018 in Excel Howtos, Learn Excel

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 »

Create your first interactive chart in Excel with this tutorial

Published on Apr 19, 2018 in Charts and Graphs, Learn Excel, Pivot Tables & Charts

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.

Continue »

VLOOKUP that fat table with ease [3 quick tips]

Published on Sep 18, 2017 in Learn Excel

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 »

Awesome chart to visualize Salary Increases for 3,500+ people [Tutorial]

Published on Aug 17, 2017 in Charts and Graphs, R programming

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?

Continue »

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.

Continue »

An odd lookup problem [Formulas]

Published on Dec 6, 2016 in Excel Challenges, Learn Excel

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 »

Sorting to your Pivot table row labels in custom order [quick tip]

Published on Nov 29, 2016 in Pivot Tables & Charts

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 »

Get FREE Excel & Power-BI Newsletter

One email per week with Excel and Power BI goodness. Join 100,000+ others and get it free.