Find MAX of Multiple Values Using VLOOKUP Function in Excel

Let’s say, you need someone’s maximum data. What is the way? How can we get specific people’s maximum data? VLOOKUP MAX of multiple values aims to determine the maximum data against specific data. To find particular data within a dataset, the VLOOKUP function is one of the best tools. MAX function with a combination of the VLOOKUP function finds the maximum data in a dataset.

Contents hide

📁 Download Excel File

Overview of VLOOKUP Function

Learn to Find MAX of Multiple Values Using VLOOKUP Function in Excel with 2 Approaches

1. Using VLOOKUP and MAX Functions to Find Max of Multiple Values

2. Using VLOOKUP and MAXIFS Functions to Find Max Value with Multiple Criteria

Apply Data Validation with VLOOKUP for Multiple Criteria and Values

How to VLOOKUP Multiple Matches and Criteria

How to VLOOKUP Multiple Values in One Cell

📄 Important Notes

📝 Takeaways from This Article

Conclusion

📁 Download Excel File

To practice and get the overview, please download the excel file:

VLOOKUP MAX of Multiple Values.xlsx

Overview of VLOOKUP Function

VLOOKUP function is the function that looks up values in an array. Any function can be combined with the VLOOKUP function to get desired output.

The general syntax of the VLOOKUP function is,

=VLOOKUP(lookup_value, table_array, column_index_num, [range_lookup])

The logical expression of the VLOOKUP function is,

=VLOOKUP(What to look up, where to look for, range containing return, return indicated as TRUE/1, or FALSE/0)

Learn to Find MAX of Multiple Values Using VLOOKUP Function in Excel with 2 Approaches

To get maximum value against specific data here we will discuss some approaches to get VLOOKUP MAX of multiple values. Here we considered a data set named Sales Data 2022. It contains the seller‘s name along with their ID, Products, selling quantity, and amount each month. The dataset also has 16 rows and 6 columns.

Method

1. Using VLOOKUP and MAX Functions to Find Max of Multiple Values

To find out the maximum amount from a dataset. You can use the VLOOKUP function where the function always lookup for data in an array. On the other hand, the MAX function always counts the maximum value that is present in the range. Follow the steps,

⬇️⬇️ STEPS ⬇️⬇️

Initially select a cell to get the output.
Then insert the following formula to measure the maximum amount.

=VLOOKUP(MAX(E6:E16),E6:F16,2,0)

🔨 Formula Breakdown

👉 Logical Expression of the formula: =VLOOKUP(MAX(range),array,col_index,0)

👉 MAX(E6:E16) finds the highest quantity 8462.

👉 Now VLOOKUP finds the amount for the highest quantity 8462 in range E6:F16 and the result is $8561.

Finally, Press Enter and get the amount of $8561 for the highest quantity.

📕 Read More: What Is Table Array in VLOOKUP in Excel?

Method

2. Using VLOOKUP and MAXIFS Functions to Find Max Value with Multiple Criteria

To find out the maximum quantity based on multiple criteria from a dataset. Suppose we need to find the selling quantity of the highest ID that sells less than 4000 units and the amount is more than $3000. You can use the VLOOKUP and MAXIFS functions where the function always lookup the maximum value in an array.

⬇️⬇️ STEPS ⬇️⬇️

Primarily select a blank cell to get the output.
After that insert the formula containing VLOOKUP and MAXIFS functions.

=VLOOKUP(MAXIFS(B6:B16,F6:F16,”<4000″,G6:G16,”>3000″),B6:G16,5,FALSE)

🔨 Formula Breakdown

👉 Here Logical Expression of the formula: VLOOKUP(MAXIFS(lookup_column,range1,”<4000″,range2,”>3000″),array,by column,FALSE)

👉 (MAXIFS(B6:B16,F6:F16,”<4000″,G6:G16,”>3000″) finds the ID of seller based on F6:F16,”<4000″ and G6:G16,”>3000″. The highest ID under this logic is 232.

👉 Now VLOOKUP runs an array B6:G16 and it returns value 0(False) until it gets the out from the 5th column based on ID.

Finally, Press Enter and get the amount of 1542 for the highest quantity of ID 232.

📕 Read More: 7 Ways to Use VLOOKUP to Compare Two Lists

Apply Data Validation with VLOOKUP for Multiple Criteria and Values

VLOOKUP function is used for data validation with multiple criteria and values. VLOOKUP function looks for the value in a defined range. But the problem is VLOOKUP function always takes the first value from the multiple if any information is germane to the look-up data. Follow the following steps.

⬇️⬇️ STEPS ⬇️⬇️

First, select a cell i.e. I10.
Next, insert the following formula.

=VLOOKUP(I7:I8,B6:F16,3,FALSE)

🔨 Formula Breakdown

👉 Here Logical Expression of the formula: =VLOOKUP(look_up_data,array,column_order,FALSE)

👉 I7:I8={“Alex”;”Sam”} looks at the data from the entire dataset.

👉 Now VLOOKUP runs an array B6:F16, takes value from the 3rd column of Product and it returns the value 0(False) until it gets out from the 3rd column based on I7:I8.

Finally, Press Enter and get the product that Alex and Sam sell Banana and Avocado.

But as we can see Alex sells bananas and mangoes; Also Sam sells Avocado, Orange, and Banana. But the VLOOKUP function only takes the first value of the required value.

How to VLOOKUP Multiple Matches and Criteria

VLOOKUP function generally matches with a single value. For lookup multiple values it is not actually VLOOKUP; Perhaps using a combination of IF, INDEX, and IFERROR a VLOOKUP function can be set up to find multiple matches.

⬇️⬇️ STEPS ⬇️⬇️

Initially, select a blank cell i.e. J6.
Secondarily, insert the following formula containing IF, INDEX, and IFERROR functions.

=IFERROR(INDEX($D$6:$D$16, SMALL(IF(1=((–($H$6=$B$6:$B$16)) * (–($I$6=$C$6:$C$16))),ROW($D$6:$D$16)-5,””),ROW()-5)),””)

🔨 Formula Breakdown

👉 Primarily, ($H$6=$B$6:$B$16) means find Melissa from $B$6:$B$16 with return value.

👉 Secondarily, ($I$6=$C$6:$C$16) dictates to match Jan from $C$6:$C$16 with return value.

👉 Thirdly, ((–($H$6=$B$6:$B$16)) * (–($I$6=$C$6:$C$16)))= {0;1;0;0;0;0;0;0;0;1;0} ; Here 1 means TRUE. On the other hand, 0 means FALSE.

👉 After that, SMALL(IF(1=((–($H$6=$B$6:$B$16)) * (–($I$6=$C$6:$C$16))),ROW($D$6:$D$16)-5,””),ROW()-5) = {2,10} that means {Orange;Mango}.

👉 Then, the INDEX function runs an array with a return value in $D$6:$D$16.

👉 Finally, IFERROR deals with the error. If an error is found, then instead of an error it returns a value.

Finally, Press Enter and get the product that Melissa sold in the month of January Orange and Mango.

How to VLOOKUP Multiple Values in One Cell

Sometimes without using VLOOKUP functions there we can run an array with a return value defining a range to look up desired values. Suppose you need to get sellers who sell multiple products and you intend to bring them together. Follow the below procedure.

⬇️⬇️ STEPS ⬇️⬇️

Primarily, select a blank cell i.e. I6.
Secondarily, insert the following formula containing TEXTJOIN, and IF functions.

=TEXTJOIN(“,”,TRUE,IF($B$6:$B$16=H6,$D$6:$D$16,””))

🔨 Formula Breakdown

👉 $B$6:$B$16= H6 means find the data of H6 in $B$6:$B$16 and also match info with $D$6:$D$16.

👉 IF($B$6:$B$16=H6,$D$6:$D$16,””)= {“Banana”;””;””;””;””;””;””;””;”Mango”;””;””}

👉 TEXTJOIN(“,”,TRUE,IF($B$6:$B$16=H6,$D$6:$D$16,””)) means join texts {“Banana”;”Mango”} by comma(,).

Lastly, by Pressing Enter, we get the product that Alex sold Banana and Mango.
Later on, if we input H7= Sam, H8= Afra, and H9= Melissa, we can get data respectively.

📄 Important Notes

🖊️ Keep in mind that, VLOOKUP data only takes the first data from multiple data.

🖊️ VLOOKUP also can be replaced by some formula combination.

🖊️ Be cautious during using multiple functions altogether. Sometimes it may massacre.

📝 Takeaways from This Article

📌 Learn the use of the VLOOKUP function along with the MAX function to get maximum values under some criteria.

📌 We also showed you how to look up data without using the VLOOKUP function.

Conclusion

In this article, we tried to demonstrate every possible way to find maximum data using VLOOKUP and MAX functions. I hope you enjoyed your learning. Queries, as well as suggestions, is appreciable. Please leave your thoughts in the comment section. For better understanding and new learning don’t forget to visit www.ExelDen.com.