How to return the highest value using the VLOOKUP function in Excel – 4 easy methods

Here's our easy to follow guide.

The microsoft excel logo on a blue background with the VLOOKUP function.

You can trust PC GuideOur team of experts use a combination of independent consumer research, in-depth testing where appropriate – which will be flagged as such, and market analysis when recommending products, software and services. Find out how we test here.

Last Updated on

If you want to know how to return the highest value using the VLOOKUP function in Excel, you’ve landed on the right page.

Let’s say you have a sales dataset and you want to retrieve the highest value without looking for it manually. You can use Vlookup to retrieve the highest value on the same sheet, from another sheet, and from multiple sheets. You can also set criteria to return the highest value.

In this guide, we’ll walk you through four different methods that can help you return the highest value using the VLOOKUP function in Excel.

How to return the highest value using the VLOOKUP function in Excel

Scenario on hand – we have a sample dataset of yearly sales of superstores belonging to a superstore chain. 

Let’s explore the four ways to use VLOOKUP to return the highest value.  

  1. Using VLOOKUP, MAX, and MIN to return value on the same sheet
  2. Utilizing VLOOKUP to return value from another sheet 
  3. Employing VLOOKUP to return value from multiple sheets
  4. Using VLOOKUP to Return value based on a criteria

Method 1 – Using VLOOKUP, MAX, and MIN to return value on the same sheet

1.1 Using MAX and VLOOKUP in conjunction

You can use the MAX function alone and in conjunction with VLOOKUP to return your desired value. 

1. We can use the MAX formula in Excel to return the highest value to an output cell. 

Here’s the formula you can input for this:

=MAX({numbers or table range})

=MAX(B7:B13) 

A screen shot of an excel spreadsheet with the highest value displayed in it.

2. Hit Enter to get the Highest Sales value from the table:

A screen shot of an Excel spreadsheet displaying the highest value found using the VLOOKUP function.

To get the Store ID and Store Name from the same table, we can use VLOOKUP in conjunction with the MAX formula. 

3. Employ the following formula in a cell to get the corresponding value:

=VLOOKUP(MAX({column range}), {table range with $}, {column index from which to return value with $}, FALSE)

=VLOOKUP(MAX($B$7:$B$13), $B$7:$D$13, 2, FALSE)

Hitting Enter will yield the corresponding text for the max value:

A screenshot of a Microsoft Excel spreadsheet showcasing the highest value obtained using the VLOOKUP function.

By changing the column index in the formula, you can also get the next corresponding value. 

We used this formula to fetch the store name from the same table range:

=VLOOKUP(MAX($B$7:$B$13), $B$7:$D$13, 3, FALSE)

Note: By using the $ sign before the row and column coordinates, we mark an absolute cell reference that we can easily drag downwards or sideways to copy the formula. 

1.2 Using MIN and VLOOKUP in conjunction

You can use the MIN function alone and in conjunction with VLOOKUP to return your desired value. 

In our case, the company wants to award the best-performing store and close down the least-performing store:

1. Calculate the Minimum sales with this formula:

=MIN({numbers or table range})

=MIN(B7:B13)

A screen shot of an excel spreadsheet with the highest value returned using the VLOOKUP function.

2. The following formulae will fetch the corresponding value:

=VLOOKUP(MIN({column range}), {table range with $}, {column index from which to return value with $}, FALSE)

=VLOOKUP(MIN($B$7:$B$13), $B$7:$D$13, 2, FALSE)

=VLOOKUP(MIN($B$7:$B$13), $B$7:$D$13, 3, FALSE)

A screenshot of a Microsoft Excel spreadsheet showcasing the result of a VLOOKUP function to return the highest value.

A screenshot of a Microsoft Excel spreadsheet showcasing the highest value obtained using the VLOOKUP function and its return.

Method 2 – Utilizing VLOOKUP to return value from another sheet 

Fetching the values from the same sheet is simple enough. But if you want to bring value from another sheet, a slight tweak will do the trick. 

In our case, the store name is entered in a separate sheet. We use the store ID in the VLOOKUP formula and the store details sheet reference to get the store name. 

1. Use this formula for this: 

=VLOOKUP({lookup value}, ‘{Sheet name}’!{column range}, {column index from which to return value}, FALSE)

=VLOOKUP(F7, ‘Store Sales’!B2:F9, 2, FALSE)

A screenshot of a table in excel showcasing the use of the VLOOKUP function to return the highest value.

Alternatively, you can also use the MAX and VLOOKUP combination to calculate a value from data in another sheet. 

Here’s the formula to use:

=VLOOKUP(MAX(‘{Sheet name}’!{column range}), ‘{Sheet name}’!{Table range}, {column index from which to return value}, FALSE)

=VLOOKUP(MAX(‘Store Sales’!P3:P9), ‘Store Sales’!P3:R9, 2, FALSE)

A screenshot of a spreadsheet in excel demonstrating the use of VLOOKUP function to return the highest value.

Method 3 – Employing VLOOKUP to return value from multiple sheets

Vlookup can also be used to return the highest value from multiple sheets.  

Imagine that the company has stores in two states – Texas and Louisiana. It wants to retrieve the highest sales value overall. 

1. You can employ the following formula to use the MAX formula to retrieve the highest value from data on multiple sheets:

=MAX(‘{First Sheet Name}’!{Column range}, ‘{Second Sheet Name}’!{Column range})

=MAX(‘Texas Store Sales’!P3:P9, ‘Louisiana Store Sales ‘!P3:P9)

A screenshot of an excel spreadsheet featuring a highlighted number, obtained using a VLOOKUP function to return the highest value.

Using this formula, we get the overall highest sale value. 

2. Next, we use the Vlookup formula to get the Store ID and Store Name. 

This formula uses an IFERROR function to loop the formula and retrieve the store with the highest sales. 

For simplification, we fetch the max value from the cell address with the highest sales value. 

Here’s the formula to use to use VLOOKUP to look through data on multiple sheets:

=IFERROR(VLOOKUP({Lookup value},”{First Sheet Name}’!{Table range},{column index from which to return value},FALSE), IFERROR(VLOOKUP({Lookup value},”{Second Sheet Name}’!{Table range},{column index from which to return value}, FALSE),”NOT FOUND”))

=IFERROR(VLOOKUP(B7,’Texas Store Sales’!P3:R9, 2, FALSE), IFERROR(VLOOKUP(B7,’Louisiana Store Sales ‘!P3:R9,2,FALSE),”NOT FOUND”))

Note: You can add multiple sheets to this formula by separating it with a comma and writing “NOT FOUND” at the end. 

A screenshot of a Microsoft Excel spreadsheet demonstrating the use of the VLOOKUP function to return the highest value.

3. Next, change the column index to get the corresponding data in another column:

A screen shot of an excel spreadsheet with a highlighted text box demonstrating the VLOOKUP function to return the highest value.

Method 4 – Using VLOOKUP to return value based on a criteria 

We can use VLOOKUP with MAXIFS to return a value based on a criteria. 

In this case, we want to retrieve the highest sales value for stores that have been in operation for at least 2 years. 

For this, insert the following formula: 

=MAXIFS({Column range},{Criteria column range},”{Criteria}”)

=MAXIFS(B7:B13,D7:D13,”>24″)

A screenshot of a Microsoft Excel spreadsheet showcasing the VLOOKUP function to return the highest value.

To get the store name, we use this MAXIFS formula in the VLOOKUP formula like this:

=VLOOKUP(MAXIFS({Column range},{Criteria column range},”{Criteria}”), {Table range}, {column index from which to return value}, FALSE)

=VLOOKUP(MAXIFS(B7:B13,D7:D13,”>24″), $B$7:$C$13, 2, FALSE)

A screenshot of a Microsoft Excel spreadsheet showcasing the utilization of the VLOOKUP function to return the highest value.

Wrapping up

We can use the VLOOKUP formula in many scenarios. Above, we laid out four ways to use VLOOKUP to retrieve the highest value. The easiest method was retrieving the value from data on the same sheet. But with large datasets, the other methods will be pretty helpful. 

Learn more about Excel and how it works with these guides: