Excel University Blog
Read on for in-depth articles, tutorials, and videos. Search or browse for specific topics. Be sure to subscribe if you'd like to be notified when we write something new.
VLOOKUP
My name is Penny Li, a cross-border tax accountant, and I often come across instances in my work where I need to convert foreign currencies to US dollar. I have decided to build a foreign currency conversion tool in Excel that is capable of generating single-day as well as average foreign exchange rates for multiple…
Read More
Overview During an Excel University webinar, Jeff gave a demonstration of Power Query that related to a company’s annual budget process. Initially, I was not familiar with Power Query but decided to learn it. Ultimately, I was able to incorporate Power Query into our annual budget process, eliminating the need for the software we were…
Read More
This is the second post in the Slow to Fast series, where we are taking incremental steps to improving our reconciliations. In the first post we improved a purely manual process with conditional formatting. That approach works when the lists are relatively small, on the same sheet, and when we are only concerned with a…
Read More
In this video, which is part of Microsoft’s Modern Excel webcast series, we’ll cover the basic syntax of FILTER and then see how it compares to VLOOKUP, INDEX/MATCH, XLOOKUP, and SUMIFS. The question is: can FILTER accomplish the tasks we typically associate with these powerful Excel functions? Note: not all versions of Excel contain the…
Read More
This is the first post in a series where we’ll talk about how to use the FILTER function as an alternative to lookup functions including VLOOKUP. Why? Well, as we’ll discover in this series, the FILTER function offers several key benefits. For example, FILTER supports: In this first post, we’ll see how the FILTER function…
Read More
In this post, we’ll discuss a way to simulate using VLOOKUP to return multiple matching rows and/or columns. What do you mean by “simulate” Jeff? Well, VLOOKUP is designed to return a single value, not multiple values. That is, VLOOKUP scans down the lookup range and stops at the first matching row … ignoring any…
Read More
In this, our final analysis, we will briefly recap each of the 8 rounds and confirm we understand the key differences between these two incredible functions. Then, we’ll confirm we know when and how to apply each in practice. Let’s get to it. Winner? So, which function is better? Which one should we always use?…
Read More
The purpose of this round is to examine the ability of each function to perform a range lookup. A range lookup is where we aren’t looking for an exact matching value, but a value that falls between a range of values. So let’s get to it. Round 8 Each sales reps earns a bonus…
Read More
The purpose of this round is to examine the issue of multiple lookup columns. Often, we have a single unique ID that we can use as the lookup value. When that is true, great. But, what happens when we need to use multiple columns for our lookup value? That is exactly what this round is…
Read More
Welcome to Round 6! In this round, we’ll discover how both functions behave when we insert a new worksheet column. In other words, does inserting a new worksheet column break the formula? Or, will it keep working? Let’s find out … the round begins now. Round 6 We would like to retrieve some information…
Read More