Find Max of Multiple Values by Using VLOOKUP Function in Excel

Get FREE Advanced Excel Exercises with Solutions!

MS Excel’s  VLOOKUP function is used when we need to find things in a table or in a range by row. It makes our task easy in terms of searching for something in the given in range. Using this somewhat versatile function we can get a maximum of multiple values in Excel. In this article, I will show various ways to find the VLOOKUP max of multiple values in Excel.


Find the Max of Multiple Values by Using the VLOOKUP Function in Excel: 2 Handy Ways

In this article, we will demonstrate to find ways to the VLOOKUP max of multiple values in Excel by combining the VLOOKUP and MAX functions and nesting the VLOOKUP and MAXIFS functions to find the maximum value.

Method 1: Combining VLOOKUP and MAX Functions to Find Max of Multiple Values

Let’s say we have a dataset of some food order lists with their Name, Quantity, Delivery date, Status, and Price. Now our task is to find out the price of the maximum quantity from the given dataset.

Combining VLOOKUP and MAX Functions to Find Max of Multiple Values in Excel 

Steps:

  • Firstly, choose the E15 cell.
  • Then, write down the following formula in cell E15.
  • After that, press Enter.
=VLOOKUP(MAX(C5:C13),C5:D13,2,0)

Formula Breakdown
  • VLOOKUP (value, table, col_index, [range_lookup]) this is the syntax of the VLOOKUP function where MAX(C5:C13) is the value, C5:D13 is the table, 2 is the col_index, and lastly, 0 is [range_lookup].
  • Here MAX(C5:C13) is our lookup value. That means the maximum value from the quantity column will be the lookup value.
  • C5:D13 is the table array or simply we can say the range where we will find the value.
  • As we have two columns that’s why 2 is used as a column index number
  • Lastly, 0 is used as we want an exact match.
  • Finally, you will get the maximum quantity price in the below image.

  • Now you can change any value from the quantity column and see the change on the output box. For instance, I have changed the Cheese quantity from 9 to 90.
  • As we have found the new quantity number 90 that’s why the maximum quantity price is changed automatically.

Read More: How to VLOOKUP Multiple Values in One Cell in Excel


Method 2: Nesting VLOOKUP and MAXIFS Functions for Finding the Max Value with Multiple Criteria

Now we will see how to find maximum values using some conditions. Our concern will be to find the name of the food which has a maximum ID where the quantity is less than 10 and the price is greater than $1200.

Nesting VLOOKUP and MAXIFS Functions for Finding the Max Value with Multiple Criteria in Excel

Steps:

  • Firstly, select the G15 cell.
  • Secondly, write down the following formula in cell F14.
  • Then, press Enter
=VLOOKUP(MAXIFS(B5:B13,D5:D13,"<10",E5:E13,">1200"),B5:G13,2,FALSE)

Formula Breakdown
  • MAXIFS(B5:B13,D5:D13,”<10″,E5:E13,”>1200″) this part will return the maximum ID number where the quantity is less than 10 and the price is greater than $1200.
  • Then the VLOOKUP function will return the Name of the matched ID. Here 2 is used as our name is in the second column. And FALSE or 0 is used to get the exact match.
  • Finally, you will find the name of the food which has a maximum ID where the quantity is less than 10 and the price is greater than $1200.

Read More: VLOOKUP to Return Multiple Values Horizontally in Excel


Reasons Behind VLOOKUP Function Not Working in Excel

Common Errors When they show
#N/A In the VLOOKUP function if range_lookup is TRUE, then if the value in the lookup_value is smaller than the smallest value in the first column of the table_array, you’ll get the #N/A error value.#REF!
#REF! If col_index_num is greater than the number of columns in table array, you’ll get the #REF! error value.
#VALUE! If the table_array is less than 1, you’ll get the #VALUE! error value.
#NAME? The #NAME? error value normally means that the formula is missing quotes.
#SPILL! This particular #SPILL! The error usually means that your formula is relying on the implicit intersection for the lookup value and using an entire column as a reference.
  • In the VLOOKUP function to declare match type, we can either use 0,1 or TRUE, FALSE.
  • If range_lookup is TRUE or left out, the first column needs to be sorted alphabetically or numerically. If the first column isn’t sorted, the return value might be something you don’t expect. Either sort the first column or use FALSE for an exact match.

Download Practice Workbook

You may download the following Excel workbook for better understanding and practice it by yourself.


Conclusion

These are some ways to find the max of multiple values by applying the VLOOKUP function in Excel. I have shown all the methods with their respective examples but there can be many other iterations. I have also discussed the fundamentals of the used functions. If you have any other method of achieving this, then please feel free to share it with us.


Related Articles


<< Go Back to VLOOKUP Multiple Values | Excel VLOOKUP Function | Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Abdullah Al Murad
Md. Abdullah Al Murad

Md. Abdullah Al Murad is a computer engineer who loves exploring Excel and VBA programming. Programming, to him, is a time-saving tool for efficiently managing data, files, and internet tasks. He is proficient in C, C++, Python, JavaScript, HTML, SQL, MySQL, PostgreSQL, Microsoft Office, and VBA and goes beyond the basics. He holds a B.Sc. in Computer Science and Engineering from American International University-Bangladesh. He has transitioned into the role of a content developer. Beyond his work, find... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo