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

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

Battle of Heavyweights: Round 5

By Jeff Lenning | October 2, 2019 |

Round 5 begins now! In this post, the goal is to understand the results of each function when no matching rows are found. That is, when the lookup value is not found in the data table. Do the functions return an error? The closest match? 0? Well, that is why we are here, so let’s…

Read More

Battle of Heavyweights: Round 4

By Jeff Lenning | September 25, 2019 |

Welcome to round 4. In this round, we want to explore the issue of return values. Specifically, the data types that our two heavyweights can return. Can they return text values? Numbers? So, that’s the idea … let’s jump right in. Round 4 … ding! Round 4  We have some account numbers, but we…

Read More