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

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) 

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

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:

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)

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)

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)

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)

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)

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. 

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

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″)

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)

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:

• How to use VLOOKUP to search text in Excel
• How to use VLOOKUP with COUNTIF
• How to use VLOOKUP to find the last value in the column in Excel