CODEWITHSUNDEEP
excelexcel-formula
user3591507
user3591507

Reputation: 1

how to get the largest value from the second column for vlookup value in first column

A B
5 25
4 10
3 15
5 100

in this case i want to vlookup the "5" from column A & answer would be 100

=+VLOOKUP("5",A:B,2,FALSE)

which give me 25

Upvotes: 0

Views: 286

Answers (3)

Prabhat Srivastava
Prabhat Srivastava

Reputation: 197

We can use this also

=SUMPRODUCT((LARGE(IF(A2:A5=5,B2:B5,""),1)))

Press Ctrl + Shift + Enter.

Upvotes: 0

Moiety Design
Moiety Design

Reputation: 473

VLOOKUP only allows you to lookup the first occurence.

Your best option is to filter the table used for VLOOKUP to order from largest to smallest based on that second column. This will force the first instance of lookup_value in VLOOKUP to be next to the highest value of that entry. The below duplicates of lookup_value all have lower column two values and hence will not be seen by VLOOKUP as the highest entries are pushed to the top.

Upvotes: 0

user3589786
user3589786

Reputation: 11

Use array formulas. For example, assuming your numbers above are in A2:B5, type the following within a single cell, and press Ctrl + Shift + Enter:

=MAX(IF(A2:A5=5,B2:B5,-1000000))

Upvotes: 1

Related Questions