CODEWITHSUNDEEP
excelexcel-formulamaxvlookupworksheet-function
SystemX17
SystemX17

Reputation: 3715

VLOOKUP return column A text where column B has the largest number

I have the following data in an Excel spreadsheet (just as example and not homework):

Item Name     Qty    Price
Apple           3    $3.00
Banana          5    $2.80
Orange          7    $1.20

I would like to know which item I have the most of. I have tried the following formula but it doesn't appear to be working:

=VLOOKUP(MAX(B:B),A:C,1, FALSE)

My question is: a) am I using VLOOKUP correctly and b) is it the right function for this?

Upvotes: 1

Views: 1111

Answers (1)

pnuts
pnuts

Reputation: 59442

I think VLOOKUP is not suitable because the Item is to the left of the Qty. Please try instead:

=INDEX(A:A,MATCH(MAX(B:B),B:B,0))

Your formula (with minor adaptation) would however be suitable to look up the Price associated with the maximum Qty (because Price is to the right of Qty): 

=VLOOKUP(MAX(B:B),B:C,2,FALSE)

Upvotes: 6

Related Questions