There are several ways to lookup data in a spreadsheet. If you're building a dashboard, you'll find this very useful.
The =XLOOKUP() function is my new favorite way to lookup data. It's discussed in the last section π.
We'll look at four of the built in lookup functions in both Excel and Google Sheets:
=LOOKUP()
=VLOOKUP()
=HLOOKUP()
=XLOOKUP()
Now, if you've spent any time in spreadsheets, you've probably heard mention of or are already using =VLOOKUP()
. It is one of the most popular functions out there, but is also a bit mystifying if you don't use it on a regular basis.
π I'll walkthrough each of these to give you a full understanding of how to use the functions properly. And I'll highlight my new favorite, =XLOOKUP()
, that Microsoft released in 2019 and Google Sheets added in 2022.
β I've also built a coffee themed Google Sheet that you can open and follow along with. Here it is.
π½οΈ And, I've got you covered...at the end of the article there's a video walkthrough too. π₯

Coffee Data
In our coffee data spreadsheet, I have made two sheets with the same data on it. One, the coffee-data
tab, is for LOOKUP
and VLOOKUP
. The horizontal-data
tab is for HLOOKUP
.
Here's what the coffee-data
tab looks like. There are columns for the coffee name, price, popularity, roast level and taste.

Here's what the horizontal-data
tab looks like. Same info, just transposed so we can walk through the HLOOKUP
function in a bit.

And then we have our main tab, lookup-functions
, where we'll examine the different functions below.

If you haven't pulled up the Google Sheet yet, go ahead so you can follow along: https://docs.google.com/spreadsheets/d/1rNAJKwGQzdq8F2zMrAwHMQvY_z3FlBs9BIy_4MIOXn4/edit#gid=1137792422
Everything works the same in Excel, but it's very easy to share Google Sheets. You can make your own copy to work in by clicking File -> Make a copy.
I've created several named ranges in this sheet to make it easier as we fill out the functions. You can examine those by clicking Data -> Named ranges. I'll reference these in the function definitions below.

How to Use the LOOKUP Function
No surprise here β =LOOKUP()
lets you look up a value in your data. Same as everything else here.
Here's our LOOKUP function to return the taste: =LOOKUP(A2,coffees,taste)
.
We're using named ranges (coffees & taste) which define the coffees column and the tasting notes column in our coffee-data
.
If you pull up the sample Google Sheet, you'll see that we're giving LOOKUP three arguments: the search_key
, search_range|search_result_array
, and the optional [result_range]
.

The search_key
is the thing we're searching for. In our example, it's the name of the coffee we want information about. All of the functions have a search_key
argument.
The search_range|search_result_array
is the range where =LOOKUP()
needs to find the search_key
. It can be used as both search and result ranges too.
Say you have search keys in your coffee column (A) and the result you want displayed is the taste column (E). If you specify A:E as the search_range|search_result_array
then you'll get the tasting notes on whatever coffee you're searching for.

When you do this, the result range value will come from the last column (or row) in the range.
The alternative is to simply specify the search_range
column on the coffee column and then enter another range for the result_range
.
This is what I've done in our sample spreadsheet since I wanted to pull data from each of the columns about the coffee.
Shortcomings of =LOOKUP()
:
- Data must be sorted. It will not work properly if it's not.
- You must specify the single return column or row for the result.
How to Use the VLOOKUP Function
This is a very popular function because it lets you lookup data with a little more power than the regular LOOKUP
function.
Here's our VLOOKUP function to return the taste: =VLOOKUP(E2,All,5,FALSE)
.
We're using another named range. This time, just one is needed. We have All
which is the entire table of coffee-data
.

With =VLOOKUP()
we enter the search_key
as before but then we give it a range
to search. The first column in the range
will be used to find the search_key
. Then we give it an index
which tells how many columns to the right we want to look for our returned value.
We then type in FALSE
to indicate that our data isn't sorted. (This is actually unnecessary here since we sorted it so that the regular LOOKUP
function would work properly).

Shortcomings of =VLOOKUP()
:
- Must lookup data from left to right.
- Must specify a number for the index. If you add or move columns in your data, you risk breaking the formula.
- Defaults to sorted data. In many cases, you'll need to set that argument as
FALSE
so the function will work properly.
How to User the HLOOKUP Function
HLOOKUP works basically the same as VLOOKUP except that instead of searching through columns, it searches through rows.
Here's our HLOOKUP function to return the taste: =HLOOKUP(I2,hAll,5,FALSE)

Check out the horizontal-data
tab to see that I've transposed the same data set so that our search_key
now is spread across a row instead of down a column. Then when we give an index
value, it returns that value by counting from top to bottom:

So if we wanted the price, we'd have index
of 2 since it's the second row down. And in our example, we return taste
with an index
of 5.
Shortcomings are the same as VLOOKUP
.
How to Use the XLOOKUP Function
Enter: =XLOOKUP()
! Microsoft released this as a successor to VLOOKUP and HLOOKUP in 2019, and Google Sheets finally added it in August of 2022.
What's different?
Well, to start with, you can use it vertically or horizontally. You don't have to specify one or the other as long as you have the proper ranges as arguments.
Check out both of the XLOOKUP
blocks on the sample spreadsheet. One is using it as a vertical lookup and the second as a horizontal lookup. All that is necessary are that the ranges are correct: The search_key
must be a single row or column, and the lookup_range
must be of the same size depending on which is used.


Some more features/advantages:
=XLOOKUP()
defaults to an exact match so you don't have to specify amatch_mode
if this is what you're after.- You're able to define a custom string to display as
missing_value
instead of#N/A
in the event that nothing is returned. - You're able to define
search_mode
to search from the last entry to the first if you desire. InVLOOKUP
andHLOOKUP
, it is only possible to go first to last. - Instead of declaring new functions for each desired value (
price
,popularity
,roast level
, andtaste
),=XLOOKUP()
will return each value in the givenlookup_range
.
If you want don't want all the values returned, you'd need to reduce the size of the range.
In the spreadsheet example, I've defined my ranges All
and hAll
to include all the columns and rows, respectively. If I wanted to leave out taste, for instance, we'd need to leave that column/row out of the lookup_range
.
=XLOOKUP()
was introduced to be the successor of =VLOOKUP()
and =HLOOKUP
. I think I'll be using it going forward, what about you?
Conclusion
As with most things in computer programming and spreadsheets, there are many ways to solve the same problem.
We've explored four ways to lookup data in a spreadsheet: =LOOKUP()
, =VLOOKUP()
, =HLOOKUP()
and =XLOOKUP()
. Each is powerful, but =XLOOKUP()
, the newest function, is particularly useful in combining and expanding many features of its predecessors.
Here is a video walkthrough of everything we've discussed:
Thanksπfor reading and watching!
Subscribeπto my YouTube channel here: https://www.youtube.com/@EamonnCottrell/
And say heyπon LinkedIn here: https://www.linkedin.com/in/eamonncottrell/