Bottom Line: Learn several shortcuts to save time when writing lookup formulas.
Skill Level: Beginner
Watch the Tutorial
Download the Excel Files
I've included both the Begin and Final files so you can follow along and try the shortcuts for yourself.
Five Shortcuts for Lookups
Below are some shortcuts that will help you to write XLOOKUP formulas faster. These techniques are also helpful for VLOOKUP and other formulas as well.
If you're not sure when to use VLOOKUP instead of XLOOKUP, watch this tutorial: XLOOKUP vs. VLOOKUP
Today's tutorial assumes you are familiar with writing VLOOKUP and XLOOKUP formulas. If that's not the case, start by checking out these posts to familiarize yourself with lookup functions.
- XLOOKUP for Excel: Explained in 3 Minutes
- The New XLOOKUP Function: Compared to VLOOKUP & INDEX MATCH in Excel
- VLOOKUP Tutorial for Excel – Everything You Need To Know
1. Selecting Range References
One frequent issue that people have when writing lookup formulas is selecting the lookup and return array ranges. This is especially true if the range is large, which makes selecting the range with your mouse cumbersome.
The shortcut to select an entire column is to first click on the beginning cell in your range and then type Ctrl + Shift + ↓. This will highlight the entire range down to the point where there is a blank cell.
This shortcut works for selecting arrays in both XLOOKUP and VLOOKUP formulas.
If your data set has lots of blank cells, we will look at an alternative when we get to Shortcut #3.
2. Making a Reference Absolute
The next shortcut for writing lookup formulas is for making a reference absolute. Absolute references, as opposed to relative references, assure that the reference doesn't change when the formula is copied down or when changes are made to the table. Instead, they always reference the exact cells that are indicated. Absolute references are denoted by placing a dollar sign ($) before the column letter and row number.
The shortcut to add those dollar signs is F4. This makes both the columns and the rows in your range absolute. You can keep hitting F4 to toggle between options for absolute columns only, absolute rows only, no absolutes, and back again to absolute columns and rows.
3. Selecting a Column in a Table
The Ctrl + Shift + ↓ shortcut mentioned above doesn't work so well when there are several blank cells in your column. You have to keep hitting the down arrow to essentially jump over those blank cells and continue selecting the entire column range.
In cases like this, I recommend turning your data range into an Excel Table. There are so many benefits to using Excel Tables, and easily selecting columns within the table is one of them. 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.
You do not need to use F4 because Excel Tables already formats the reference as absolute.
If you want to learn more about the benefits of using Excel Tables, try these tutorials:
- Excel Tables Tutorial Video – Beginners Guide for Windows & Mac
- 5 Reasons to Use an Excel Table as the Source of a Pivot Table
- Best Practices for Naming Excel Tables
4. Selecting the First Column in Row
If you are working with really wide tables, or your lookup table is on a separate sheet, this shortcut may save you some time.
Let's say you are writing a formula in the last column of a wide table, but your lookup reference is in the first column. It could take some time to use your left arrow to navigate back to that column. Instead, you can use the Home key as a shortcut.
Home will move your selection to the cell in the first column of your sheet (in the same row you are already in).
What if your table doesn't start in Column A of your sheet? In that case, you can use the keyboard shortcut Ctrl + ← instead. Keep in mind that blank cells in your table may require you to hit ← more than once in order to get to the first column in your data set.
5. Navigating Between Sheets
Finally, I want to show you how to move to the next sheet in your workbook using your keyboard. The shortcut is Ctrl + Page Down. To move to the previous sheet, you can use Ctrl + Page Up.
You can hold Ctrl and press Page Up or Page Down repeatedly to continue to select the next sheet in the workbook.
I hope this tutorial helps you to save time and prevent errors. If you found this post helpful, you might also benefit from this video: 7 Keyboard Shortcuts for Selecting Cells and Ranges in Excel. If you have any questions, you can ask them in the comments section and we'll do our best to answer them quickly. Have a great week!