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!
Control + Backspace to return to the cell where you are writing the formula
I noticed in the video that your workbook had a tab regarding whole-column refs that you didn’t touch on. I use that a lot (one click in the column header to select the entire column). I realize this doesn’t necessarily work for vertically stacked data sources with different data in those same columns, but I rarely have this, so whole-column referencing works for me most of the time. It allows me (even without using the formal table structure) to add data to the list without having to resize my range in the lookup formula. And it accounts for blank cells since it selects the whole column. Is there a disadvantage to using this method as opposed to selecting the exact range of data? Just wondering why you didn’t mention it.
For an entire column IN the data range, not all one million cells in it, but there are going to be enough blank cells to be a bother, or just ’cause it’s a habit now, I click the column label header, the “B” for column B, say. That highlights the whole column and puts “B:B” into the formula.
Then I press “Shift-End-Up Arrow” which pulls up the bottom highlight to the actual last non-blank cell in the column (which is what I want in these cases).
Finally, to dress up the top when writing a formula that will choke on the data range’s column header, I highlight the “B1” (in this example) in the “B1:B24034” it placed into the formula and click the actual first cell I desire, perhaps B2. Or just edit it by hand from B1 to B2.
It doesn’t work so sweetly for multiple columns at once unless the first column in, say, “B:D” has the lowest non-blank cell as the “Shift-End-Up Arrow” will work using the first shown column’s situation. But…
Still in the formula, building that range, you can highlight the ending cell’s address in the range (using the above, that could be D24034) and click the column header. Then use “Shift-End-Up Arrow” and the proper cell for it will now be there so you have something like:
and just edit out the middle reference (either “:D1” or “D1:”) which will have changed to “:D1” (as in it now looks like “B2:D1:D24999”). Or just edit it to have the same starting row as the first reference: Excel doesn’t care if your reference has 30 terms in it (this has three) but it will create the largest “box” needed to hold all the referenced cells so it would take that one and change the B2 to B1 to fit D1 inside. Change the D1 to D2 and your box is as desired. But why change it and live with “funny…” when you can just edit it out, eh?
However, you CAN go through your range column by column this way and end up with the right box by including each one’s contribution as you go from left to right. So four columns done one by one might yield “B2:B24034:C12888:D21777:E23774” and instead of dealing with finding the right box, you let Excel figure out that it needs “B2:E24034” which it will apply in the formula regardless of the oddness of your ending range.
Used to be Excel would replace these ranges with the resulting Start:End range, but those days seem gone.
I have not tried it in a long, long time, but it used to work with dynamic creation of ranges, just creating the end point, and having the range be a Start cell and however many column end points one needed. It was one way of finding the dynamic end for a range, but other ways won the hearts and minds of Excel website makers.
All I really need much of nowadays is the basic one column selection of an end point.
But the basic one column selection has no extra considerations. So easy peasy.