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

Why do you ‘need’ to use vlookup ?

What kind of question is that?

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

An assignment??

Hi!

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

Use Ctrl+Shift+Enter to enter the formula

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

Markmzz

Hi markmzz,

Appreciate the reply. Thats the answer I want. But why is '{1;2}' at the end? Shouldn't it substitute the column index number?

Why is it written as, '=SUM(LARGE(VLOOKUP?' Why not, '=SUM(VLOOKUP(LARGE?'

And how would I know this was an array function as opposed to a function?

 

[Jonmo1]

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

Why do you ‘need’ to use vlookup ?

What kind of question is that?

A Pretty standard, and very common one on support forums like this.

Believe it or not, we're all trying to help you.
Focusing on what you think the solution should be is counter productive to solving your problem.
You're overlooking perfectly suitable solutions to your task at hand, and ignoring them because they don't look like what you think the solution should be.
Did you even consider that vlookup might not be the best tool for the job?

We're not here to do your bidding. We are here to help you find solutions to a problem.
"I want to use vlookup to do xyz" is not a 'problem'. It's a solution seeking a problem.

"I want to find the sum of the 2 largest values for the name in E9", That is a 'Problem'.
The suggested solutions using index and match is a solution to that problem.


You don't take your car to an auto mechanic and say "I want to use this distributor cap to solve my poorly running engine". Then when the mechanic says "the problem isn't the distributor cap, it's your clogged fuel injectors". Do you then say, "but why can't you use the distributor cap to fix it?"

If you don't know what the actual solution is (this thread wouldn't exist if you did), how can you so easily dismiss suggestions ?

 

[markmzz]

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

Hi markmzz,

Appreciate the reply. Thats the answer I want. But why is '{1;2}' at the end? Shouldn't it substitute the column index number?

Why is it written as, '=SUM(LARGE(VLOOKUP?' Why not, '=SUM(VLOOKUP(LARGE?'

And how would I know this was an array function as opposed to a function?

Hi!

I will try to expain.

Lets go:

1) {1;2} is used to get the first and second highest values found by the VLOOKUP function.

2) Because first I need to get the wanted value for related values after calculating the first and second largest values. For Example: First a lookup Daniel and found 45.99, 23.78 and 51.02. After that, calculating the first and second largest value 45.99 and 51.02<strike></strike>. And finally, calculete the sum.

3) Because the VLOOKUP function with the argument {2;3;4} get initially three values( array) to not only a single value.

I hope that this helps.

By the way, Jonmo1's remark is correct. The Index and Match function do the job too.

Markmzz

 

[bencar]

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

Believe it or not, we're all trying to help you.
Focusing on what you think the solution should be is counter productive to solving your problem.

Yes I agree with you and for the most part youre correct. But if I ask for vlookup countless times upon answers that have nothing to do with it, something's not right here, unless vlookup is impossible to apply to my issue which obviously its not.

I'm not trying to give you a hard time. I do appreciate your help and presence on my thread, youre a very smart man. But I did want vlookup.

Hi!


1) {1;2} is used to get the first and second highest values found by the VLOOKUP function.

2) Because first I need to get the wanted value for related values after calculating the first and second largest values. For Example: First a lookup Daniel and found 45.99, 23.78 and 51.02. After that, calculating the first and second largest value 45.99 and 51.02<strike></strike>. And finally, calculete the sum.

3) Because the VLOOKUP function with the argument {2;3;4} get initially three values( array) to not only a single value.

I hope that this helps.

By the way, Jonmo1's remark is correct. The Index and Match function do the job too.

Markmzz

Hi markmzz,

1) Yes I know {1;2} is used for getting the first and second highest values. But I want to know isn't it in the 'column index number' part of vlookup? After all its where the columns need to be referenced in the vlookup cormula.

2) But shouldnt the order of operation be 1st, find the largest 2 values.. 2nd sum them.. lastly, look them up.

3) WHat dio you mean by, "with the argument {2;3;4} get initially three values( array)". Are youre saying when i add a function into vlookup it automatically becomes an array function? Could you explaijn this and how would I know how to recognize an array function?

Lastly... why is $A$2:$D$5 in absolute and not relative reference?

 

[markmzz]

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

Hi markmzz,

1) Yes I know {1;2} is used for getting the first and second highest values. But I want to know isn't it in the 'column index number' part of vlookup? After all its where the columns need to be referenced in the vlookup cormula.

2) But shouldnt the order of operation be 1st, find the largest 2 values.. 2nd sum them.. lastly, look them up.

3) WHat dio you mean by, "with the argument {2;3;4} get initially three values( array)". Are youre saying when i add a function into vlookup it automatically becomes an array function? Could you explaijn this and how would I know how to recognize an array function?

Lastly... why is $A$2:$D$5 in absolute and not relative reference?

Hi!

Lets try again (now with steps):

	A	B	C	D	E	F	G	H	I	J	K
1	Thrower	Throw 1	Throw 2	Throw 3		Thrower	Result		Formula for Daniel	=SUM(LARGE(VLOOKUP(F2,$A$2:$D$5,{2;3;4},0),{1;2}))
2	Daniel	45,99	23,78	51,02		Daniel	97,01		Step 1	VLOOKUP("Daniel",$A$2:$D$5,{2;3;4},0) - in this part of the formula, I will used the VLOOKUP function to get the values of the col 2, col 3 and col 4 ({2;3;4}) of the row of Daniel (first row - A2:D2) in the range $A$2:$D$5 ({45,99;23,78;51,02}). The range $A$2:$D$5 must to be absolute because I will copy down the formula (for Richard, Matt and Stevie).
3	Richard	40,22	42,32	43,69		Richard	86,01		Step 2	LARGE({45,99;23,78;51,02},{1;2}) - in this part of the formula, I will used the LARGE function to get the first and the second ({1;2}) largest values of the values 45,99; 23,78 and 51,02 that I get with the VLOOKUP function ({51,02;45,99}).
4	Matt	37,55	34,2	31,5		Matt	71,75		Step 3	=SUM({51,02;45,99}) - in this part of the formula, I will used the SUM function to sum the values that I get with the LARGE function 51,02 and 45,99.
5	Stevie	69,43	0	56,11		Stevie	125,54		Step 4	=97,01 - in this part of the formula, I will get the final value.
6
***	********	********	********	********	**	*********	********	**	******************	*******************************************************	**

<tbody>
</tbody>

I hope that this helps.

Markmzz

 

[killz]

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

1) Why is ​

[COLOR=var(--Font-dark-background-light-text-add-on-fg)!important][COLOR=var(--Font-dark-background-light-text-add-on-fg)!important]$A$2:$D$5 [/COLOR]in absolute reference? Why not relative reference?

2) Why are there semi-colons between the values? Why not commas?
[/COLOR]​

 

[markmzz]

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

Hi Killz,

Questions:

1) Why do you say $A$2:$D$5 must to be absolute to copy down the formula (for Richard, Matt and Stevie)? Its a table array. Table arrays arent "copied down." How are you copying a table array?

2) Why do you use semi-colons in between the values? Why not commas?

Answers:

Lets go:

1) If you use relative reference (A2:D5), look at you will get below when you copy the formula in G2 (=SUM(LARGE(VLOOKUP(F2,A2:D5,{2;3;4},0),{1;2}))) down:

=SUM(LARGE(VLOOKUP(F3,A3:D6,{2;3;4},0),{1;2}))
=SUM(LARGE(VLOOKUP(F4,A4:D7,{2;3;4},0),{1;2}))
=SUM(LARGE(VLOOKUP(F5,A5:D8,{2;3;4},0),{1;2}))

The result is ok in this case (not the formula), but if you change the data in the rows in the range A2:D5 you will have problem. Try it and tell me the results.

2) I can use semi-colons (;) and commas (,) in this case. Example: I can use {2;3;4} and {1;2} or I can use {2,3,4} and {1,2}. Try it and tell me the results.

I hope that this helps.

Markmzz