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.

## π Download Excel File

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

## 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.

**π Read More: 5 Ways to Extract Filtered Data into Another Excel Sheet**

## 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**.

**π Read More: 6 Solutions If VLOOKUP Returns #N/A When Match Exists in Excel**

## 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.

**π Read More: 8 Ways to VLOOKUP to Return Multiple Values Vertically in Excel**

## π 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**.

## Related Articles

**VLOOKUP to Find Duplicates in Two Columns in Excel****Excel Formula: Use VLOOKUP Function with Multiple Sheets****5 Examples to Use IF and VLOOKUP as Nested Function in Excel****2 Ways to Use VLOOKUP Function with Two Lookup Values****3 Quick Ways to Vlookup and Return Multiple Values Horizontally**