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

[bencar]

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))

	A	B	C	D
Daniel
Richard
Matt
Stevie

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #002060"]Thrower[/TD]
[TD="bgcolor: #002060"]Throw 1[/TD]
[TD="bgcolor: #002060"]Throw 2[/TD]
[TD="bgcolor: #002060"]Throw 3[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]45.99[/TD]
[TD="align: right"]23.78[/TD]
[TD="align: right"]51.02[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]40.22[/TD]
[TD="align: right"]42.32[/TD]
[TD="align: right"]43.69[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]37.55[/TD]
[TD="align: right"]34.2[/TD]
[TD="align: right"]31.5[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]69.43[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]56.11[/TD]

</tbody>

​

 

[AlanY]

try

=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

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

confirm with shift-control-enter

 

[bencar]

try

=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

=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]

is it for your homework?

 

[bencar]

is it for your homework?

Come on dude.. this isnt high school stuff I'm asking. Get serious

 

[bencar]

Any way to use LARGE in VLOOKUP?

 

[bencar]

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))

	A	B	C	D
Daniel
Richard
Matt
Stevie

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #002060"]Thrower[/TD]
[TD="bgcolor: #002060"]Throw 1[/TD]
[TD="bgcolor: #002060"]Throw 2[/TD]
[TD="bgcolor: #002060"]Throw 3[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]45.99[/TD]
[TD="align: right"]23.78[/TD]
[TD="align: right"]51.02[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]40.22[/TD]
[TD="align: right"]42.32[/TD]
[TD="align: right"]43.69[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]37.55[/TD]
[TD="align: right"]34.2[/TD]
[TD="align: right"]31.5[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]69.43[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]56.11[/TD]

</tbody>

Sheet1​

 

[ebea]

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]

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

Maybe something like this


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][td="bgcolor: #DCE6F1"]

G​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Thrower​

[/td][td]

Throw 1​

[/td][td]

Throw 2​

[/td][td]

Throw 3​

[/td][td][/td][td]

Thrower​

[/td][td]

Result​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

Daniel​

[/td][td]

45,99​

[/td][td]

23,78​

[/td][td]

51,02​

[/td][td][/td][td]

Daniel​

[/td][td]

97,01​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

Richard​

[/td][td]

40,22​

[/td][td]

42,32​

[/td][td]

43,69​

[/td][td][/td][td]

Matt​

[/td][td]

71,75​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

Matt​

[/td][td]

37,55​

[/td][td]

34,2​

[/td][td]

31,5​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

Stevie​

[/td][td]

69,43​

[/td][td]

0​

[/td][td]

56,11​

[/td][td][/td][td][/td][td][/td][/tr]
[/table]

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

M.

 

[Fluff]

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