Using VLOOKUP in array formulae

[2016LM]

Hi

Many thanks for taking the trouble to read this Thread.I’ve tried searching the forum for a similar problem / question, but to no avail,hence the new Thread.




I have a row of values (A5:A9, shownin black) I want to look-up (using VLOOKUP) in a small table (A1:B3, shown in red) and then sum thesefigures at the bottom of the table in cell B14 (shown in green).




[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]1

[/TD]
[TD]7

[/TD]
[/TR]
[TR]
[TD]2

[/TD]
[TD]10

[/TD]
[/TR]
[TR]
[TD]3

[/TD]
[TD]12
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]7
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]7
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]12
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]12
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]10
[/TD]
[/TR]
[TR]
[TD]TOTAL
[/TD]
[TD]46
[/TD]
[/TR]
</tbody>[/TABLE]


Iappreciate I can do this using VLOOKUP in each of the cells adjoining the A5:A9 range then total with a SUM, whatI was wondering however was, is it possible to use VLOOKUP in an array formula? I tried using the following formula: {=SUM(VLOOKUP(A5:A9,$A$1:$B$3,2,TRUE)}which seems to sum the first value only.



I’mstill on the ‘array formula’ learning curve, and was hoping someone could pointout where my mistake is and / or whether what I’m trying to achieve is possiblewith array formulae?



Manythanks,



2016LM

 

[steve the fish]

Why do we get 46 as the sum? Do you mean this?

=SUMPRODUCT(SUMIFS($B$5:$B$9,$A$5:$A$9,$A$1:$A$3))

 

[2016LM]

Hi steve the fish,

Thanks for replying to my query. 46 is the summation of the numbers in the second column (shown in black); these have been looked-up from the table in red. Apologies I mistakenly quoted the total being is cell B14, when it should have stated B10. The table was included to show what I was trying to achieve with the array formula - I was effectively wanting to do away with having to look-up the numbers individually then summing then i.e. instead of the column with 7,7,12,12,10 and the 46, I would simply have the numbers to look-up (i.e. 1,1,3,3,2) and a total. Is this possible to do?

Apologies fo the untidiness of my initial post, I was having difficulty with the text editor.

Kind regards,
2016LM

 

[XOR LX]

Hi,

Achieving this with VLOOKUP is possible, though not very efficient (nor syntactically easy to understand). Better is SUMIFS, i.e.:

=SUMPRODUCT(SUMIFS(B1:B3,A1:A3,A5:A9))

For the record:

=SUMPRODUCT(VLOOKUP(N(IF({1},A5:A9)),A1:B3,2,0))

where the entries in A5:A9 are assumed to be numeric, not text.

Regards

 

[2016LM]

Hi XOR LX

That's what I was after, brilliant thanks. Out of interest, could you explain what 'N(IF({1},A5:A9))' does please; I've never seen an IF command with a logical_test and either a TRUE or FALSE before?

Apologies to all, I've just realised that 46 should have been 48.

Kind regards,

2016LM