Professor Excel

Performance of XLOOKUP: How fast is the new XLOOKUP vs. VLOOKUP?

We’ve explored the basics of XLOOKUP, discovered a wide range of advanced use cases for XLOOKUP and the advantages towards VLOOKUP and INDEX/MATCH. What we have not talked about so far is the performance of XLOOKUP. Especially for larger Excel models with possibly hundreds of thousands of functions to calculate, performance is a major concern. Let’s see how XLOOKUP performs vs. VLOOKUP and INDEX/MATCH!

[…]

XLOOKUP in Excel

XLOOKUP in Excel: Easily Solve all VLOOKUP Problems!

It seems as if Microsoft has listened to many complaints of Excel users and introduced a new formula: XLOOKUP. It’s supposed to improve all the disadvantages of the “traditional” lookup functions VLOOKUP, INDEX/MATCH and SUMIFS. This article describes in what case and how to use it. Please feel also free to download all example in an Excel workbook at the end of this article.

The XLOOKUP function is special in a way that it is very simple in it’s basic form. Just three arguments that are quite straight-forward. At the same time, it has very advanced capabilities. That’s why I’ve decided to split this article: The basic application, just using the first three arguments and advanced functions.

[…]

Convert XLOOKUP to VLOOKUP in Excel.

Convert XLOOKUP to VLOOKUP With These 4 Easy Methods!

The new XLOOKUP formula in Excel is great. It’s very powerful and solves many problems the iconic VLOOKUP (or HLOOKUP) formula cannot do. But there is one problem: It’s only available in the newest Excel versions. Older versions – which are still around a lot – can’t use them. In this article we explore 4 ways to convert XLOOKUP to VLOOKUP.

[…]

Case-Sensitive Lookups in Excel

Case-Sensitive Lookups in Excel: 4 Methods (+XLSX-Download)

By definition, the VLOOKUP formula is not case-sensitive. Case-sensitive means, that it matters if you use capital letters or small letters. For instance, a VLOOKUP search for “AAA” will return the same value as for “aaa” or “Aaa”. But in some cases, you want to differentiate between capital and small letters. So how do you proceed? In this article, you learn how to make VLOOKUP, HLOOKUP, INDEX/MATCH and SUMIFS case-sensitive.

[…]

VLOOKUP to the Left

VLOOKUP to the Left in Excel. Yes, It’s Possible!

The VLOOKUP formula in its base version only works from left to right. The search column must be located on the left-hand side of the return column. What if your data doesn’t have such structure? There is a way for using the VLOOKUP to the left but it requires an array form of the formula. It’s often worth considering alternative formulas though. Here is everything you should know.

[…]

Multi-Condition Lookups in Excel

Multi-Condition VLOOKUP and INDEX/MATCH (+Excel-Download)

There are many cases in which you want to conduct a lookup with several search criteria. As of now only the SUMIFS formula allows a multi-condition lookup. Unfortunately, SUMIFS only works for numeric values (including dates) as the return value. If you want to return text, there is no direct method.

[…]

3d, 3 way, lookups, 3d lookups, excel

3D Lookups in Excel: 6 Methods (+Download)

The king of lookups in Excel: The 3 way- or 3D lookup. Imagine this scenario: You got several Excel tables, each has rows and columns. Depending on your input values, you want to get the data from a specific cell from the right table, row and column. Such lookups are called 3D lookups or 3 way lookups. In this article we explore 6 methods of how to conduct 3D lookups in Excel.

[…]

2D Lookups in Excel: 4 Easy Methods for 2 Way Lookups + Excel Download

There are many cases in which you need a 2 dimensional lookup. That means, if you want to get a value from a specific row-column combination with neither rows or columns fixed. Unfortunately, the problem of a two way lookup comes up quite often. In this article we explore 4 methods of how to conduct 2D lookups in Excel.

[…]

vlookup, hlookup, lookup, excel, how, guide

VLOOKUP in Excel: All You Need to Know About the Powerful Formula

One of the most popular formulas in Excel is the VLOOKUP formula. Many lookup approaches are based on the VLOOKUP formula. Mastering it is crucial for any of the following chapters and methods. Unfortunately, VLOOKUP is not as easy to use as a SUM or COUNT. In this article, you learn how to use VLOOKUP, what to keep in mind and some more advices about VLOOKUP.
[…]

index, match, excel, formula, vlookup

INDEX and MATCH: The Alternative to VLOOKUP in Excel

You’ve probably heard of the VLOOKUP formula in Excel, haven’t you? The VLOOKUP formula searches for a value in a column. Once found it returns another value from the same row. A combination of INDEX and MATCH serves the same purpose. It works slightly different and has therefore some advantages and disadvantages towards VLOOKUP. 

[…]