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>

​

 

[Aladin Akyurek]

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

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

This is your own formula in post #1 :

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

What do you think you have in E9?

 

[bencar]

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

This is your own formula in post #1 :

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

What do you think you have in E9?

What do I "think" I have in E9? Theres nothing in E9. After I hit enter I get an error message. But nothing in E9.

 

[Aladin Akyurek]

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

What do I "think" I have in E9? Theres nothing in E9. After I hit enter I get an error message. But nothing in E9.

VLOOKUP expects a look up value it can look for in a table. Let's leave this E9 business.

What is the expected value for the data in A2:D5 you posted?

 

[bencar]

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

VLOOKUP expects a look up value it can look for in a table. Let's leave this E9 business.

What is the expected value for the data in A2:D5 you posted?

Seriously I dont know what youre talking about. I really dont. Youre saying things which go over my head. If you dont know the answer thats fine. I'll wait for someone who does.

 

[Aladin Akyurek]

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

Seriously I dont know what youre talking about. I really dont. Youre saying things which go over my head. If you dont know the answer thats fine. I'll wait for someone who does.

You posted this data in post #1 ...

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

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

2​

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

45.99​

[/td][td]

23.78​

[/td][td]

51.02​

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

3​

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

40.22​

[/td][td]

42.32​

[/td][td]

43.69​

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

4​

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

37.55​

[/td][td]

34.2​

[/td][td]

31.5​

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

5​

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

69.43​

[/td][td]

0​

[/td][td]

56.11​

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

And you said:

"I'm trying to use vlookup to lookup a value that sums up the 2 largest numbers in columns B,C and D."

So, I'm asking: What is the value that must be returned?

 

[Marcelo Branco]

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

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

The combination of INDEX and MATCH is the proper solution to your problem because the INDEX function is able to return an array of values provided we use a 0 (zero) as its 3rd argument (column_num)

For clarity I'll repeat the solution in post 9

[TABLE="class: grid"]
<tbody>[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]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/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}))

Observe that a 0 is used as the 3rd parameter of the INDEX function. This zero forces the INDEX function to return an array of values, that is, the values of all columns of the range, in the row corresponding to the lookup_value used in the MATCH function. For example, when the lookup_value is Daniel, the index function returns B2:D2; in the case of Matt it returns B4:D4.

This array is passed to the LARGE function that returns the largest and the second largest.

At last, SUMPRODUCT adds these two values.

To see what the formula does, step by step, try Formulas > Evaluate formula

M.

 

[KJoker]

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

for someone asking a question and expecting help, original poster is quite irritable

 

[Jonmo1]

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

I needed vlookup not match index.

Why do you ‘need’ to use vlookup ?

 

[markmzz]

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>

​

Hi!

If I understand correctly what you want, then the formulas below can helps:

=SUMPRODUCT(LARGE(VLOOKUP(E9,$A$2:$D$5,{2;3;4},0),{1;2}))

Use Ctrl+Shift+Enter to enter the formula

=SUM(LARGE(VLOOKUP(E9,$A$2:$D$5,{2;3;4},0),{1;2}))

Markmzz

 

[snb_]

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

Why do you ‘need’ to use vlookup ?

Because that's how it is formulated in the assignment.