How Vlookup to return the max value from multiple hits in Excel

How Vlookup to return the max value from multiple hits in Excel

We have several mathematical functions provided in or by the Microsoft Excel. Some of these functions are; v-lookup, min, max, mean, mode, and median. They all have their basic function to perform in the excel sheet.

V-lookup or vertical lookup as the name suggests is a mathematical function that does the searching of data in multiple columns and compares the data with other columns. This function does the vertical search, unlike the h-lookup which on the other hand does a horizontal lookup.

The v-lookup is also used to get the minimum and the maximum values in different columns in the excel sheets.

We have a couple of steps to achieve the maximum value using the v-lookup function. These steps include the ones below;

Step 1

We need to have an excel sheet where we will record our values, to have this, open a blank excel sheet on your laptop from Microsoft Excel. Record some values in the excel sheet, you can use for example a column for quantity and price as in the case below.

Step 2

We already have out data recorded in an excel sheet, the second step will be the use the v-lookup function together with the MAX function. These two functions are now going to get for us the largest value in our data set.

Generally, the formula for obtaining the maximum value from the excel sheet we have created will be; =VLOOKUP (MAX (A2: A7), A2: B7, 2, 0).To explain this formula, there are two columns, A and B. We will do a data search from column A and cell A2 to column B cell B7. In the maximum range for lookup, we use 0 which at times is written as false and the 2 in the formula stands for the two columns.

Writing this formula on the formula bar or the result cell you will be able to find the largest or rather the maximum value in the data set above. This is how it should look like.

From the excel sheet above, 1500 is the maximum value on that data set.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.