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

<tbody>
</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

	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​

<tbody>
</tbody>

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.