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>

​

 

[bencar]

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

Oh god...

 

[bencar]

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

Maybe something like this


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #0E1823"][/TD]
[TD="bgcolor: #0E1823"]

A​

[/TD]
[TD="bgcolor: #0E1823"]

B​

[/TD]
[TD="bgcolor: #0E1823"]

C​

[/TD]
[TD="bgcolor: #0E1823"]

D​

[/TD]
[TD="bgcolor: #0E1823"]

E​

[/TD]
[TD="bgcolor: #0E1823"]

F​

[/TD]
[TD="bgcolor: #0E1823"]

G​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #0E1823"]

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: #0E1823"]

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: #0E1823"]

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: #0E1823"]

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: #0E1823"]

5​

[/TD]
[TD]

Stevie​

[/TD]
[TD]

69,43​

[/TD]
[TD]

0​

[/TD]
[TD]

56,11​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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

M.

And where is vlookup in your formula?

 

[Marcelo Branco]

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

And where is vlookup in your formula?

I used INDEX MATCH instead of VLOOKUP

M.

 

[Sean15]

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

Hi Marcelo Branco,

I am following this thread. I can't get your INDEX and MATCH to work. Could you see below?

Excel 2010
	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	#NUM!
3	Richard	40,22	42,32	43,69		Matt	#NUM!
4	Matt	37,55	34,2	31,5
5	Stevie	69,43	0	56,11
6
Sheet1

Cell Formulas
Range	Formula
G2	=SUMPRODUCT(LARGE(INDEX(B$2:D$5,MATCH(F2,A$2:A$5,0),0),{1;2}))
G3	=SUMPRODUCT(LARGE(INDEX(B$2:D$5,MATCH(F3,A$2:A$5,0),0),{1;2}))

 

[Jonmo1]

Try

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


FYI, try not to be locked into what you think the solution should be.
i.e., don't expect a solution to use specific function a or b, there are many different ways to skin the same cat.

Instead, focus on what results you're looking for.
I would have been very helpful if you posted what your expected results where, and including what the contents of E9 is.

Excel 2013/2016
	A	B	C	D	E	F
1	Thrower	Throw 1	Throw 2	Throw 3
2	Daniel	45.99	23.78	51.02
3	Richard	40.22	42.32	43.69
4	Matt	37.55	34.2	31.5
5	Stevie	69.43	0	56.11
6
7
8
9					Matt	71.75
10					Daniel	97.01
11					Stevie	125.54
12					Richard	86.01
Sheet1

Cell Formulas
Range	Formula
F9	=SUM(LARGE(INDEX($B$2:$D$5,MATCH(E9,$A$2:$A$5,0),0),{1,2}))
F10	=SUM(LARGE(INDEX($B$2:$D$5,MATCH(E10,$A$2:$A$5,0),0),{1,2}))
F11	=SUM(LARGE(INDEX($B$2:$D$5,MATCH(E11,$A$2:$A$5,0),0),{1,2}))
F12	=SUM(LARGE(INDEX($B$2:$D$5,MATCH(E12,$A$2:$A$5,0),0),{1,2}))

 

[Sean15]

@Jonmo1 - thank you very much. Upon further review, it seems that my sample data in post #14 was formatted as text so formula returned #NUM !
Sorry about that. Formula in post #9 and #15 returns the required values.

 

[bencar]

@Jonmo1 - thank you very much. Upon further review, it seems that my sample data in post #14 was formatted as text so formula returned #NUM !
Sorry about that. Formula in post #9 and #15 returns the required values.

DO NOT hijack teh thread! If you need help with your problem create a thread.

 

[bencar]

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

I used INDEX MATCH instead of VLOOKUP

M.

I'm confused. I needed vlookup not match index.

 

[Aladin Akyurek]

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

@ Post #1

What do you have in E9 you are looking up?

 

[bencar]

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

@ Post #1

What do you have in E9 you are looking up?

E9? I dont have anything in E9. I just want to make this possible with vlookup not match index.