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

Battle of Heavyweights: Round 3

By Jeff Lenning | September 10, 2019 |

Welcome to Round 3. In this round, we want to explore the idea of column order. That is, we want to find out if the order of the lookup table columns matter to these functions. Let’s find out! Round 3 … begins now! Round 3  We are trying to build a little report, and…

Read More

XLOOKUP

By Jeff Lenning | August 29, 2019 |

Raise your hand if you love VLOOKUP? Me too! We Excel users have grown to love VLOOKUP over the years. But, XLOOKUP offers some advantages. XLOOKUP? That’s not a typo. Depending on your version of Excel and when you are reading this, you may see this function immediately, or it may be a while before…

Read More

Battle of Heavyweights: Round 2

By Jeff Lenning | August 19, 2019 |

Round 2 begins! In this round, the goal is to understand what happens when values that are supposed to match are stored as different data types. Different data types? Cells can store many kinds of data, including numbers, text, dates, times, and so on. The format you apply to a cell doesn’t impact the data…

Read More

Battle of Heavyweights: Round 1

By Jeff Lenning | August 7, 2019 |

It is time! In the first post, we met VLOOKUP. In the second post, we met SUMIFS. And now, the battle begins! Here is how it will work. The competition will consist of several rounds. In each round, we will use VLOOKUP and then SUMIFS to perform the same task. Then, we’ll determine who wins…

Read More

Battle of Heavyweights: SUMIFS

By Jeff Lenning | July 24, 2019 |

This is the second post in the Battle of Heavyweights series, where we are comparing VLOOKUP to SUMIFS in order to understand their key differences. Knowing these key differences will help us determine which to use in a given workbook. In the previous post, we were introduced to VLOOKUP. Now, it is time to meet…

Read More

Battle of Heavyweights: VLOOKUP

By Jeff Lenning | July 24, 2019 |

This is the first post in a series called Battle of Heavyweights. In the series, we’ll use a boxing metaphor to compare VLOOKUP and SUMIFS. Now, as you read the next paragraph, use your best announcer’s voice and be sure to imagine the roar of a cheering crowd 🙂 “Ladies and gentlemen, welcome to the…

Read More

VLOOKUP on Multiple Columns and Return Text

By Jeff Lenning | March 15, 2018 |

You want to perform a lookup with VLOOKUP, but, there are multiple lookup columns. So, what are you supposed to do? Combine them into a single lookup column? That is certainly one option, but, as with just about anything in Excel, there are multiple ways. In a previous post, I showed one way to do…

Read More

How to Build a PivotTable with the Data Model

By Jeff Lenning | February 22, 2018 |

Traditional PivotTables are an incredible feature of Excel, but, they are not without limits. Many of the typical restrictions are removed when you use the data model rather than a single Excel table. If you’d like to learn how to build a PivotTable using the data model, and learn what the data model is, strap…

Read More

VLOOKUP Hack #10: Maslow’s Hammer

By Jeff Lenning | January 10, 2018 |

Psychologist Abraham Maslow wrote a line that is often referred to as Maslow’s Hammer. It goes something like this: “I suppose it is tempting, if the only tool you have is a hammer, to treat everything as if it were a nail.” I’ve heard the simplified version more often: “If all you have is a…

Read More

VLOOKUP Hack #9: Partial Match

By Jeff Lenning | January 3, 2018 |

Let’s say we want VLOOKUP to match the lookup value “North Region” with “North Region Subtotal” stored in the lookup range. We started this series by looking at the 4th argument. We know it can be TRUE or FALSE. FALSE means exact match and TRUE means approximate match. So, what exactly is an approximate match?…

Read More