Mastering XLOOKUP: Find Largest Values in Excel

Jeremy Jul 21, 2026

The 'XLOOKUP' function in Excel, introduced in Office 365 and Excel 2021, is a powerful tool for data retrieval, replacing the traditional 'VLOOKUP' and 'HLOOKUP' functions. One of its standout features is the ability to retrieve the largest value in a range, making it an invaluable asset for data analysis and reporting.

XLOOKUP function in #excel better than VLOOKUP
XLOOKUP function in #excel better than VLOOKUP

In this article, we delve into the 'XLOOKUP' function's capability to find the largest value in a dataset, providing step-by-step guides and practical examples to help you harness this capability effectively.

How to use XLOOKUP in Excel?
How to use XLOOKUP in Excel?

Understanding XLOOKUP's Largest Value Capability

The 'XLOOKUP' function offers three modes for data retrieval: 'exact', 'approximate', and 'custom'. When seeking the largest value, we'll focus on the 'custom' mode, which allows us to specify that we want to find the 'next larger' or 'next smaller' item.

Two Way Lookup In Excel
Two Way Lookup In Excel

To retrieve the largest value, we'll set 'mode' to spacious double quotes, effectively telling the function to search for the 'next larger' item, which in our case will be the largest value in the range.

Retrieving the Largest Value in a Single Column

Vlookup with Multiple Columns in Excel 🎯Vlookup Multiple Values-Vlookup Multiple Criteria #excel
Vlookup with Multiple Columns in Excel 🎯Vlookup Multiple Values-Vlookup Multiple Criteria #excel

Suppose we have a range of data (e.g., A2:A10) with various values and we want to find the largest number. We can enter the following formula: '=XLOOKUP("", A2:A10, "")'.

Here, the formula will ignore the first argument, search for the 'next larger' item (the largest value) in the range, and return it. Replace 'A2:A10' with your range of cells.

Retrieving the Largest Value in Multiple Columns

the vlookup vs xolockup comparison is shown in this screenshot
the vlookup vs xolockup comparison is shown in this screenshot

The 'XLOOKUP' function can also retrieve the largest value from a two-dimensional range (e.g., a table). In this case, we can use a similar formula: '=XLOOKUP("", B:E, "")'.

This will search for the 'next larger' item across columns B to E and return the largest value in that range. Ensure you adjust the range to match your specific data layout.

Advanced Techniques with XLOOKUP and Largest Values

Excel Lookups Made EASY! Master VLOOKUP, HLOOKUP, and XLOOKUP in 10 MIN
Excel Lookups Made EASY! Master VLOOKUP, HLOOKUP, and XLOOKUP in 10 MIN

While the basic usage of 'XLOOKUP' for finding the largest value is straightforward, the function offers more advanced capabilities when combined with other formulas or functions.

For instance, we can identify the cell containing the largest value, determine its position, or perform further calculations based on the largest value retrieved.

How to Use VLOOKUP Function in Excel (8 Suitable Examples) - ExcelDemy
How to Use VLOOKUP Function in Excel (8 Suitable Examples) - ExcelDemy
the advanced vlookup in excel
the advanced vlookup in excel
a green background with the words lookup versus vlookup
a green background with the words lookup versus vlookup
an excel chart with the number and type of columns in each column, as well as numbers
an excel chart with the number and type of columns in each column, as well as numbers
VLOOKUP Vs. INDEX/MATCH - The Debate Ends Here
VLOOKUP Vs. INDEX/MATCH - The Debate Ends Here
22 Essential Excel Formulas for Data Analysis | Waqar Khan posted on the topic | LinkedIn
22 Essential Excel Formulas for Data Analysis | Waqar Khan posted on the topic | LinkedIn
the highest health per person in each country
the highest health per person in each country
Forget VLOOKUP in Excel: Here's Why I Use XLOOKUP
Forget VLOOKUP in Excel: Here's Why I Use XLOOKUP
the names of large numbers are shown
the names of large numbers are shown
How to filter using vlookup in excel
How to filter using vlookup in excel
two type mismatch screenshots with the text'your lookup value is next, but the table has numbers or vice vesa
two type mismatch screenshots with the text'your lookup value is next, but the table has numbers or vice vesa
a green sign with the words 5 advanced vlookup tricks you must know
a green sign with the words 5 advanced vlookup tricks you must know
the united states ten dollar bill has been printed on it's front and back side
the united states ten dollar bill has been printed on it's front and back side
two photos with hands on each side of the same person and one has his fist raised
two photos with hands on each side of the same person and one has his fist raised
Absolute Value
Absolute Value
Top 49 of the Best Excel Shortcuts You Need to Know
Top 49 of the Best Excel Shortcuts You Need to Know
World's Largest Chicken Breeds
World's Largest Chicken Breeds
Add a Barcode
Add a Barcode
XLS vs XLSX: Difference and Comparison
XLS vs XLSX: Difference and Comparison
four different types of money are shown in green and black frames with the same image on them
four different types of money are shown in green and black frames with the same image on them

Identifying the Cell with the Largest Value

To find the cell address containing the largest value, we can modify the basic formula like so: '=INDIRECT(XLOOKUP("", A2:A10, ADDRESS(1:1, SEQUENCE(ROW(A2:A10))), , "up") + 1)'.

The 'INDIRECT' function, along with 'ADDRESS' and 'SEQUENCE', returns the reference of the cell containing the largest value.

Calculating the Position of the Largest Value

To determine the position (i.e., the row or column number) of the largest value, we can use the basic 'XLOOKUP' formula in combination with the 'MATCH' function.

For instance, to find the row number, use: '=MATCH(XLOOKUP("", A2:A10, ""), A2:A10, 0)'. This will return the row number of the cell containing the largest value.

In conclusion, mastering the 'XLOOKUP' function's ability to retrieve the largest value in a range empowers you to streamline data analysis, make insightful decisions, and create more dynamic and efficient Excel workbooks. Keep exploring the function's capabilities to unlock further potential and enhance your Excel prowess.