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.

XLOOKUP

Find Last Occurrence

By Jeff Lenning | July 28, 2020 |

I was recently asked how to find the last occurrence of a value in a column. Although there are multiple ways to accomplish this, in this post, I’ll talk about how to use XLOOKUP. Objective Before we jump into Excel, let’s confirm our objective. Let’s say we have a bunch of transactions that look like…

Read More

Dynamic Arrays Article

By Jeff Lenning | May 5, 2020 |

Microsoft has been hard at work updating Excel’s calculation engine. Depending on the version of Excel you’re using, you either have—or will have when you update—access to some capabilities that are sure to change how you think about and use Excel. I wrote about one of the new capabilities called Dynamic Arrays for California CPA…

Read More

XLOOKUP Webinar

By Jeff Lenning | March 16, 2020 |

XLOOKUP is the next-gen lookup function designed to address the limitations of VLOOKUP and related workarounds. It combines the capabilities of numerous functions including VLOOKUP, INDEX, MATCH, HLOOKUP, and IFERROR. Many have asked me to teach this via webinar. So, I created this short webinar that covers the basics 🙂 This on-demand webinar is about…

Read More

XLOOKUP CalCPA Article

By Jeff Lenning | February 11, 2020 |

VLOOKUP is perhaps the most iconic function in Excel and users have come to adore it. As great as VLOOKUP is, it has several limitations. For example, column order matters. It was designed to look for a matching value in the first (left-most) column within the lookup range. Once found, it scans to the right…

Read More

XMATCH

By Jeff Lenning | October 23, 2019 |

When Microsoft released XLOOKUP, they also released XMATCH. XMATCH hasn’t received as much attention as XLOOKUP, so I wanted to talk about it here. In summary, it is similar to the legacy MATCH function we’ve been using for decades, but it receives similar enhancements introduced with XLOOKUP. Let’s get to it. XMATCH Before we get…

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

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