All articles with 'vlookup' Tag
How to compare two Excel sheets using VLOOKUP? [FREE Template]
![How to compare two Excel sheets using VLOOKUP? [FREE Template]](https://chandoo.org/wp/wp-content/uploads/2024/07/SNAG-0063-2.png)
You are the boss of ACME Inc. And one day, both of your accounts receivables team members Sara and James come to you with two versions of the customer payment data. How do you compare these two Excel sheets and reconcile the data? In this article, let me explain the step by step process.
Continue »What is XLOOKUP and how to use it in Excel?
![What is XLOOKUP and how to use it in Excel?](https://chandoo.org/wp/wp-content/uploads/2024/03/SNAG-0041.png)
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
![VLOOKUP(), MATCH() and INDEX() – explained in plain English](https://chandoo.org/wp/wp-content/uploads/2008/11/vlookup-match-index-syntax.png)
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 »Speed up your Excel Formulas [10 Practical Tips]
![Speed up your Excel Formulas [10 Practical Tips]](https://chandoo.org/wp/wp-content/uploads/2012/03/SNAG-2595.png)
Excel formulas acting slow? Today lets talk about optimizing & speeding up Excel formulas. Use these tips & ideas to super-charge your sluggish workbook. Use the best practices & formula guidelines described in this post to optimize your complex worksheet models & make them faster.
1. Use tables to hold the data
2. Use named ranges & named formulas
3. Use Dynamic Array formulas
4. Sort your data
5. Use manual calculation mode
… and more. Read on to learn these top 10 tips & ideas to improve performance of your excel formulas.
Continue »Range Lookup in Excel – How to lookup the pricing tier? [Formulas]
![Range Lookup in Excel – How to lookup the pricing tier? [Formulas]](https://chandoo.org/wp/wp-content/uploads/2010/06/range-lookup-excel.png)
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]
![Write a formula to get Department Budget for a Month [Homework]](https://chandoo.org/wp/wp-content/uploads/2021/03/department-budget-2d-lookup-problem.png)
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 »Image Lookup – How-to show dynamic picture in a cell [Excel Trick]
![Image Lookup – How-to show dynamic picture in a cell [Excel Trick]](https://chandoo.org/wp/wp-content/uploads/2020/12/image-lookup-in-Excel-vlookup-but-for-images.jpg)
Do you ever want to have an image or picture lookup in Excel? Something like the depiction above.
In this article, learn how to set up an image lookup using Excel. You can use this to display staff details, product images or machine parts etc.
Continue »Top 10 Excel formulas for IT people
![Top 10 Excel formulas for IT people](https://chandoo.org/wp/wp-content/uploads/2020/06/top-10-excel-formulas-for-IT-people-h.jpg)
Are you in IT & use Excel often? This article explains top 10 formulas for IT professionals. Useful for project managers, IT analysts, Testing people and BAs.
We cover a 10 practical situations and explore various Excel formulas to solve them. Example workbook provides more details too.
Continue »Lookup last non blank value – Excel Challenge
![Lookup last non blank value – Excel Challenge](https://chandoo.org/wp/wp-content/uploads/2020/06/lookup-last-non-blank-value.png)
I have a fun Excel lookup challenge for you. You have data as shown below and want to find the last non blank value for a given account number. For example, for acct number 2015, the answer would be Freedom. How would you solve this? Refer to this workbook for 3 possible answers. Just move […]
Continue »VLOOKUP or INDEX+MATCH? – Excel Interview Question – 01
![VLOOKUP or INDEX+MATCH? – Excel Interview Question – 01](https://chandoo.org/wp/wp-content/uploads/2019/08/vlookup-or-index-match.png)
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
![VLOOKUP multiple matches – trick](https://chandoo.org/wp/wp-content/uploads/2019/04/vlookup-multiple-matches.png)
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 »![Top 10 Excel Formulas for any situation](https://chandoo.org/wp/wp-content/uploads/2019/03/top-10-formulas-meme.jpg)
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 »Lenient lookup [Advanced Formula Trick]
![Lenient lookup [Advanced Formula Trick]](https://chandoo.org/wp/wp-content/uploads/2018/09/lenient-lookup.gif)
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 »![Mutual Fund Portfolio Tracker using MS Excel](https://chandoo.org/wp/wp-content/uploads/2018/07/mutual-fund-tracker-excel.png)
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 »Beautiful Budget vs. Actual chart to make your boss love you
![Beautiful Budget vs. Actual chart to make your boss love you](https://chandoo.org/wp/wp-content/uploads/2018/05/budget-vs-actual-chart-free-template.png)
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 »