Using XLOOKUP with tables and also with ranges of cells that are not formatted as tables

Excel’s XLOOKUP function is a powerful tool that allows you to look up a value in a table or range, and return a corresponding value from another column in the same table or range. In this tutorial, I’ll show you how to use XLOOKUP with tables in Excel.

First, let’s start by defining what a table is in Excel. A table is a range of cells that has been formatted with Excel’s Table feature. To create a table, select the range of cells that you want to convert to a table, and then go to the Insert tab on the ribbon and click on the Table button. You can then choose your table style and give your table a name.

Now, let’s look at how to use XLOOKUP with tables. Let’s say we have a table called “ProductSales” that contains the following data:

We want to use XLOOKUP to look up the sales for “Product2”. Here’s how we would do it:

1. Select the cell where you want to display the result (let’s say it’s cell C2).
2. Type the XLOOKUP formula: =XLOOKUP(“Product2”,ProductSales[Product],ProductSales[Sales])
3. Press Enter.

Let’s break down what’s happening in this formula. The first argument, “Product2”, is the value we want to look up. The second argument, ProductSales[Product], is the column in the table that contains the values we want to match against. The third argument, ProductSales[Sales], is the column in the table that contains the values we want to return.

When we press Enter, Excel will search the first column of the “ProductSales” table for “Product2”. When it finds it, it will return the corresponding value from the second column of the table, which is 200.

If the value you want to look up is not found in the table, XLOOKUP will return a #N/A error. To handle this, you can use the IFNA function to return a custom message. Here’s an example:

=IFNA(XLOOKUP(“Product4″,ProductSales[Product],ProductSales[Sales]),”Product not found”)

This formula will search the “ProductSales” table for “Product4”. If it’s not found, it will return the custom message “Product not found”.

You can also use XLOOKUP to perform approximate matches. To do this, you need to specify the “match mode” as the fourth argument of the function. The match mode can be one of four values:

• 0 (exact match)
• -1 (exact or smaller match)
• 1 (exact or larger match)
• 2 (wildcard match)

For example, if you want to find the sales for a product that falls within a certain price range, you could use an approximate match. Here’s how:

1. Create a new table called “ProductPrices” that contains the following data:

2. Type the XLOOKUP formula: =XLOOKUP(15,ProductPrices[Price],ProductPrices[Product],,-1)
3. Press Enter.

This formula will search the “ProductPrices” table for a value that is equal to or smaller than 15 in the “Price” column. When it finds the corresponding value, it will return the value from the “Product” column, which is “Product1”. Note that we have left the third argument blank in this example because we want to return the value from the first column of the table.

You can also use XLOOKUP to perform left lookups, which means that you can search for a value in the second column of a table and return a value from the first column. To do this, you simply need to swap the positions of the second and third arguments in the XLOOKUP formula.

Here’s an example:

1. Create a new table called “ProductNames” that contains the following data:

2. Type the XLOOKUP formula: =XLOOKUP(“Product2”,ProductNames[Product],ProductNames[Name])
3. Press Enter.

This formula will search the “ProductNames” table for “Product2” in the second column, and when it finds it, it will return the corresponding value from the first column, which is “Sarah”.

In addition to using XLOOKUP with tables, you can also use it with ranges of cells that are not formatted as tables. To do this, you simply need to specify the range of cells instead of the table name in the XLOOKUP formula.

Here’s an example:

1. Create a range of cells that contains the following data:

2. Type the XLOOKUP formula: =XLOOKUP(“Product2”,$A$1:$B$3,2)
3. Press Enter.

This formula will search the range A1:B3 for “Product2” in the first column, and when it finds it, it will return the corresponding value from the second column, which is 200.

In conclusion, XLOOKUP is a powerful tool that can help you quickly and easily look up values in tables or ranges of cells in Excel. Whether you’re working with tables or ranges, XLOOKUP can save you time and effort by automating the process of finding and returning the data you need.