Can't Find Summed Value of 2 Largest Numbers in Vlookup

bencar

Banned user
Joined
Jun 8, 2016
Messages
149
I'm trying to use vlookup to lookup a value that sums up the 2 largest numbers in columns B,C and D. I tried these 2 formulas on my own but thet dont work. I also tried using them as array functions. But still doesnt work. How can I make this work?

=sum(VLOOKUP(E9,A2:D5,{LARGE(B2:D2,{1,2})},0))
=sum(VLOOKUP(E9,A2:D5,(LARGE(B2:D2,{1,2}),0))



ABCD
1ThrowerThrow 1Throw 2Throw 3
2Daniel45.9923.7851.02
3Richard40.2242.3243.69
4Matt37.5534.231.5
5Stevie69.43056.11

<tbody>
</tbody>

 
Last edited:

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,185
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
try

Code:
=LARGE(INDEX($B$2:$D$5,MATCH(E9,$A$2:$A$5,0),),1)+LARGE(INDEX($B$2:$D$5,MATCH(E9,$A$2:$A$5,0),),2)

or

Code:
=SUM(LARGE(INDEX($B$2:$D$5,MATCH(E9,$A$2:$A$5,0),),{1,2}))

confirm with shift-control-enter
 
Last edited:

bencar

Banned user
Joined
Jun 8, 2016
Messages
149
try

Code:
=LARGE(INDEX($B$2:$D$5,MATCH(E9,$A$2:$A$5,0),),1)+LARGE(INDEX($B$2:$D$5,MATCH(E9,$A$2:$A$5,0),),2)

or

Code:
=SUM(LARGE(INDEX($B$2:$D$5,MATCH(E9,$A$2:$A$5,0),),{1,2}))

confirm with shift-control-enter



Thats not it. I need to use vlookup.
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,185
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
is it for your homework?
 

bencar

Banned user
Joined
Jun 8, 2016
Messages
149

ADVERTISEMENT

How Do I Find Summed Values of 2 Largest Numbers in Vlookup

I'm trying to use vlookup to lookup a value that sums up the 2 largest numbers in columns B,C and D. I tried these 2 formulas on my own but thet dont work. I also tried using them as array functions. But still doesnt work. How can I make this work??

=sum(VLOOKUP(E9,A2:D5,{LARGE(B2:D2,{1,2})},0))
=sum(VLOOKUP(E9,A2:D5,(LARGE(B2:D2,{1,2}),0))



ABCD
1ThrowerThrow 1Throw 2Throw 3
2Daniel45.9923.7851.02
3Richard40.2242.3243.69
4Matt37.5534.231.5
5Stevie69.43056.11

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1
 

ebea

Board Regular
Joined
Jul 12, 2008
Messages
206
Office Version
  1. 2010
Platform
  1. Windows
Re: How Do I Find Summed Values of 2 Largest Numbers in Vlookup

What is it for a value you lookup in E9? For the sum of the two largest: =SUMPRODUCT(LARGE(B2:D2;{1;2}))
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,393
Re: How Do I Find Summed Values of 2 Largest Numbers in Vlookup

Maybe something like this


A
B
C
D
E
F
G
1
Thrower​
Throw 1​
Throw 2​
Throw 3​
Thrower​
Result​
2
Daniel​
45,99​
23,78​
51,02​
Daniel​
97,01​
3
Richard​
40,22​
42,32​
43,69​
Matt​
71,75​
4
Matt​
37,55​
34,2​
31,5​
5
Stevie​
69,43​
0​
56,11​

Formula in G2 copied down
=SUMPRODUCT(LARGE(INDEX(B$2:D$5,MATCH(F2,A$2:A$5,0),0),{1;2}))

M.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,518
Office Version
  1. 365
Platform
  1. Windows
Re: How Do I Find Summed Values of 2 Largest Numbers in Vlookup

@bencar
Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
I have merged both threads
 

Watch MrExcel Video

Forum statistics

Threads
1,109,304
Messages
5,527,912
Members
409,792
Latest member
shawnash

This Week's Hot Topics

Top