VLOOKUP Max of Multiple Values (With Alternative)

VLOOKUP Max of Multiple Values (With Alternative)

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 use VLOOKUP, in combination with other Excel functions, to find the maximum of multiple values in a range. Along with that, I will also show the alternative way of this process.

Download the Practice Workbook

2 Ways to Find VLOOKUP Max of Multiple Values in Excel

1. Get VLOOKUP Max of Multiple Values Using VLOOKUP and MAX Functions

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.

Get VLOOKUP Max of Multiple Values Using VLOOKUP and MAX Functions

Step 1: Enter the following formula in cell E14 and press Enter

=VLOOKUP(MAX(C4:C12),C4:D12,2,0)

Formula Explanation

  • VLOOKUP (value, table, col_index, [range_lookup]) this is the syntax of the VLOOKUP function where MAX(C4:C12) is the value, C4:D12 is the table, 2 is the col_index, and lastly 0 is [range_lookup]. If you want to explore more about this VLOOKUP function, you can visit this link
  • Here MAX(C4:C12) is our lookup value. That means the maximum value from the quantity column will be the lookup value.
  • C4:D12 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.

Enter the following formula in cell E14 and press Enter

Step 2: Now you can change any value from the quantity column and see the change on the output box. For an instance, I have changed the Cheese quantity from 9 to 90

VLOOKUP Max of Multiple Values

As we have found the new quantity number 90 that’s why the maximum quantity price is changed automatically.

2. Get VLOOKUP Max of Multiple Criteria Using VLOOKUP and MAXIFS Functions

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.

Get VLOOKUP Max of Multiple Criteria Using VLOOKUP and MAXIFS Functions

Step 1: Enter the formula in cell F14 and press Enter

=VLOOKUP(MAXIFS(A4:A12,C4:C12,"<10",D4:D12,">1200"),A4:F12,2,FALSE)

Formula Explanation

  • MAXIFS(A4:A12,C4:C12,”<10″,D4:D12,”>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.

Enter the formula in cell F14 and press Enter

Alternative Option

In this section, we will see how we can get the VLOOKUP max of multiple values without using the VLOOKUP function.

For this, we will use only MAX and IF functions. Let’s have a food list with their different prices. Now we will find out the maximum price of the entered food name.

Alternative Option of VLOOKUP

Step 1: Enter the following formula in cell G7

=MAX(IF(B4:B18=G6,C4:C18))

Formula Explanation

  • IF(B4:B18=G6, C4:C18) this will find the food names which will be matched with our enter name. To explore more about this function, you can visit this link
  • Then MAX function will select the price which is the maximum among the selected names. If you want to learn more about the MAX function, you can visit this link

Formula with IF and MAX function

[ Note: Make sure the Maximum Price cell is in the correct currency format.]

Step 2: Now type any name in the G6 cell and press Enter. Here I will enter Butter in that cell

Now type any name in the G6 cell and press Enter. Here I will enter Butter in that cell

Things to Remember

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

Conclusion

These are some ways to find the VLOOKUP max of multiple 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.

Md. Abdullah Al Murad

Hello! Welcome to my Profile. Currently, I am working and researching Microsoft Excel, and here I will be posting articles related to this. My last educational degree is BSc, and my program was Computer Science and Engineering from American International University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo