**Bottom Line:** Learn the most common error with XLOOKUP and how to fix it.

**Skill Level:** Beginner

## Watch the Tutorial

## Download the Excel File

## #VALUE! Error

When working with XLOOKUP functions, there are **two main errors** that I've encountered, and **both can be avoided using the same solution.** Let's look first at the **#VALUE! error**.

When you see a #VALUE! error in your XLOOKUP function, the most likely reason is that **your** **lookup array and your return array are not the same size**.

In my example, the reason **the arrays are different sizes** is that there is a blank cell at the end of my return array. When I wrote the formula, I used the `Crtl` + `Shift` + `β` shortcut to jump to the end of my column, and that last blank cell wasn't part of the selection.

As you can see, my lookup array **ends with row 1003** while my return array **ends with 1002**.

The `Crtl` + `Shift` + `β` shortcut is one that I covered in my last tutorial: Shortcuts for Writing XLOOKUP and VLOOKUP Formulas.

Before we talk about a solution, let's look at another error you might encounter: **the Absolute Reference error**.

## Absolute Reference Error

This error isn't indicated by a **# prefix**. It's noticeable from **mistakes in the returned results**.

The reason for the error is that I **forgot to hit F4 to make my values absolute **when I wrote my XLOOKUP function. This can happen when writing either the lookup range reference or the return range reference, or both.

## The Solution: Excel Tables

The solution for both of these errors lies in** making your data set into an Excel Table**. By using an Excel Table, **your columns will always be the same size and your references will always be absolute**.

To change a table into an Excel Table you can use `Ctrl` + `T` or use the **Format as Table** button on the **Home** tab of the Ribbon.

Once your data is converted to an Excel Table, you can use the keyboard shortcut `Ctrl` + `Space` to **select the entire column** when writing your lookup formula.

If you prefer to **use your mouse** instead of a keyboard shortcut, you can **hover your mouse over the top of the column header **until your cursor becomes a **small down arrow**. Then left-click to select the column.

## Conclusion

For a succinct but helpful **overview of XLOOKUP**, check out this post: XLOOKUP for Excel: Explained in 3 Minutes.

And if you're not sure **when to use VLOOKUP instead of XLOOKUP**, you can watch this tutorial: XLOOKUP vs. VLOOKUP.

If today's post about lookup formula errors was helpful for you, if you have questions, or if your XLOOKUP is returning other errors/incorrect results, leave a comment and let us know.

Rather than selecting an array and making an absolute reference, why not just select the entire column? You donβt need to be in a table to select the entire column. What am I missing?

Hi Cary,

Great question! These are typically referred to as whole column references. I was going to cover them in this post/video, but decided to do a separate video on this topic.

There are a few risks to using whole column references.

They have the potential to slow down the calculation speed of your file and can produce incorrect results if you or other users of your file accidentally add data below the used range.

I’ll cover the pros & cons in more detail in the upcoming post.

I hope that helps. Thanks again and have a nice day!

Hey Jon, I definitely like the table approach, especially as the tables are dynamic. Another method I have been using is by columns ‘xlookup(M1,B:B,D:D,””)’. Is this OK to use or could it be problematic?

Hey Craig,

Great question! These are referred to as whole column references. Cary had the same question on this post. Check out my reply to Cary to see some of the potential issues.

I’ll be covering this in more detail in an upcoming post.

Thanks! π

I work a lot with Excel files which were exported by an ERP system. The structure is not very consistent, sometimes columns are shifted or deleted. and there is not ascending or descending order.

My favorite function threfore are combinations of Offset and Match, so I can find a specific header, then the row and the values I need.

AFIK the XLookup cannot do this.

Hey Heinz,

Great question! If you are referring to an INDEX(MATCH(),MATCH()) formula that does a lookup both horizontally and vertically, then XLOOKUP can be used for this as well.

You can actually use two XLOOKUPs. One nested inside the other. Here is the formula with descriptions for each argument.

XLOOKUP(vertical lookup value, vertical lookup array, XLOOKUP(horizontal lookup value, horizontal lookup array, entire table array))

You basically use the second XLOOKUP in the return array argument for the first XLOOKUP. The second XLOOKUP finds and returns the correct return column of values to the first XLOOKUP.

In my opinion, this formula is less intuitive than INDEX(MATCH(),MATCH()). However, once you understand it, it’s easy to write and actually a bit shorter than the INDEX formula.

I’ll cover this in more detail in a future post and video.

I hope that helps. Thanks again and have a nice day! π

I was hoping you were going to cover my value error. For the life of me I can’t figure out why my Xlookup is getting this value error now I am using multiple columns in my look up array. Also these multiple columns are not part of a table. Do I have to make my look up array a table? What I’m trying to accomplish is taking a date My look up value. Find it in my Look up array. My look up array has 7 days in one week on 1 row in 7 different columns and then the next week starts on the next row so on and so forth. Then I have the return array aligned with same number of rows as a text value that I would like returned depending on which row the date is found.

TIA