Professor Excel

2D XLOOKUPs: How to use the new XLOOKUP for searching in two dimensions

The new XLOOKUP function in Excel not only offers great advanced features, but can be also used for 2D XLOOKUPs. Before XLOOKUP, the most common way for searching in rows and columns at the same time was INDEX/MATCH/MATCH. A combination of XLOOKUP and XLOOKUP can do the same. Let’s see how it works and explore the advantages and disadvantages vs. INDEX/MATCH/MATCH.

As mentioned in the previous parts of this series, XLOOKUP returns arrays (which is a range of cells, not only a single value). VLOOKUP, on the other hand, only returns single values. The basic idea is to use one XLOOKUP to return a range of cells (for example a column or row). A second XLOOKUP wrapped around then returns a single item from this array.

[…]

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.

[…]

Return Number Format Codes in Excel

Return Number Format Codes in Excel – 4 Ways to Get the Formatting Code from a Cell

Excel is a great software. It’s easy to use (at least the basic functions…) and very flexible. Unfortunately, coming with the flexibility, users tend to misuse the options and disobey certain basic rules. One thing I’ve seen multiple times is to transport important information in the formatting of a cell. It might be the background color, font color or strike-through. In this article, we’ll talk about something related: Return number format codes.

Problem: Information is stored in the number format code of the Excel cell, for example the currency "USD" or "EUR".

Problem: Information is stored in the number format code of the Excel cell, for example the currency “USD” or “EUR”.

Let’s assume the following example. You receive a table containing prices with two columns. The first column contains the name of the item, for example “Product A”. The second column contains the respective prices. The problem is that instead of having one common currency, the currency information is only given in the number format code. Learn four methods in this article of how to return number format codes in Excel.

Please note: If you want to know how to use custom number format codes, please refer to this article. On this page we only explore how to return number format codes.

[…]

FIELDVALUE formula in Excel

FIELDVALUE Formula in Excel: Insert Data of Companies and Countries (+Download)

Excel has – in it’s newest version – a quite useful new formula type. It’s called “linked data” and offers the functionality to automatically insert data from the internet to your table. This can be done with the FIELDVALUE formula and works in a first test quite well. Unfortunately, the available data types and options are limited so far. But let’s see how it works first.

[…]

Array Formulas in Excel

Array Formulas in Excel: All You Need to Know

Array formulas are an advanced topic in Excel. Usually Excel users discover them when reaching the limits of – let’s call them – normal formulas, e.g. SUM, VLOOKUP, COUNT and so on. This article provides an introduction of array formulas in Excel.

[…]

Count Number of Unique Records in Excel

Count Number of Unique Records in Excel: 5 Methods (+Download)

A common task in Excel is to find out the number of different entries in a list. For example, you have a list of names and want to know, how many different people are listed as some people might be multiple times on the list. This article introduces 5 different methods of counting the number of unique records in a list, regarding two major differences.

  1. You simply want to know the number of unique records. There is no other condition to be considered.
  2. You want to know the number of different entries under one or more conditions.

[…]

FREQUENCY function in Excel

FREQUENCY Formula in Excel: Everything You Should Know (+Download)

FREQUENCY can classify data into bins. The formula in Excel is an array formula and just returns an array of values. In practice, it appears rather seldom. It is usually used within other formulas and not as a stand-alone formula. That said, let’s explore the purpose and usage of FREQUENCY.

[…]

Return Blank Cells Instead of Zeroes in Excel Formulas

If the return cell in an Excel formula is empty, Excel by default returns 0 instead. For example cell A1 is blank and linked to by another cell. But what if you want to show the exact return value – for empty cells as well as 0 as return values? This article introduces three different options for dealing with empty return values.

[…]