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

Convert Foreign Currencies to USD

By Penny Li | January 4, 2022 |

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

Excel for Budgeting

By Moss Levenson | November 23, 2021 |

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

Slow to Fast 2

By Jeff Lenning | February 15, 2021 |

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

FILTER vs VLOOKUP, INDEX/MATCH, XLOOKUP, SUMIFS

By Jeff Lenning | October 8, 2020 |

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

Perform Lookups with FILTER 1

By Jeff Lenning | September 23, 2020 |

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: Multiple return columns Multiple lookup columns Multiple matching row values…

Read More

VLOOKUP Return Multiple Matching Rows and Columns

By Jeff Lenning | November 13, 2019 |

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

Battle of Heavyweights: Final Analysis

By Jeff Lenning | November 5, 2019 |

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

Battle of Heavyweights: Round 8

By Jeff Lenning | October 22, 2019 |

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

Battle of Heavyweights: Round 7

By Jeff Lenning | October 16, 2019 |

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

Battle of Heavyweights: Round 6

By Jeff Lenning | October 9, 2019 |

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