Ask Your Question

[solved] help with vlookup function [closed]

asked 2013-09-25 16:05:52 +0100

will2b gravatar image

updated 2013-09-25 16:22:07 +0100

Hi, I am looking for some help to simplifiy the way I am using the Vlookup function. I currently have two tables, sheets 01 & 02, 01 contains a list of references in column D (a-z) & relating totals in column I. The formula then reads user input references on sheet 02 and returns the corresponding sum from sheet 01 (eg. B+G+J+K+Z=?)

This is the formula which works but is very bloated: =SUM(VLOOKUP(B3,'sheet 01'.$A$4:$I$60,4,0)+VLOOKUP(C3,'sheet 01'.$A$4:$I$60,4,0)+VLOOKUP(D3,'sheet 01'.$A$4:$I$60,4,0)+VLOOKUP(E3,'sheet 01'.$A$4:$I$60,4,0)+VLOOKUP(F3,'sheet 01'.$A$4:$I$60,4,0)+VLOOKUP(G3,'sheet 01'.$A$4:$I$60,4,0)+VLOOKUP(H3,'sheet 01'.$A$4:$I$60,4,0)+VLOOKUP(I3,'sheet 01'.$A$4:$I$60,4,0)+VLOOKUP(J3,'sheet 01'.$A$4:$I$60,4,0)+VLOOKUP(K3,'sheet 01'.$A$4:$I$60,4,0)+VLOOKUP(L3,'sheet 01'.$A$4:$I$60,4,0)+VLOOKUP(M3,'sheet 01'.$A$4:$I$60,4,0)+VLOOKUP(N3,'sheet 01'.$A$4:$I$60,4,0)+VLOOKUP(O3,'sheet 01'.$A$4:$I$60,4,0)+VLOOKUP(P3,'sheet 01'.$A$4:$I$60,4,0)+VLOOKUP(Q3,'sheet 01'.$A$4:$I$60,4,0)+VLOOKUP(R3,'sheet 01'.$A$4:$I$60,4,0)+VLOOKUP(S3,'sheet 01'.$A$4:$I$60,4,0)+VLOOKUP(T3,'sheet 01'.$A$4:$I$60,4,0)+VLOOKUP(U3,'sheet 01'.$A$4:$I$60,4,0)+VLOOKUP(V3,'sheet 01'.$A$4:$I$60,4,0)+VLOOKUP(W3,'sheet 01'.$A$4:$I$60,4,0)+VLOOKUP(X3,'sheet 01'.$A$4:$I$60,4,0)+VLOOKUP(Y3,'sheet 01'.$A$4:$I$60,4,0)+VLOOKUP(Z3,'sheet 01'.$A$4:$I$60,4,0))

What I would like to do is to combine the lookups in to one string eg: =SUM(VLOOKUP(B3:Z3,'sheet 01'.$A$4:$I$60,4,0)

But this, & all the variants that I have tried so far, returns an error.

Any help you can give me would be much appreciated. - Will

edit retag flag offensive reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2015-11-10 00:04:44.977406

1 Answer

Sort by » oldest newest most voted

answered 2013-09-25 16:21:27 +0100

will2b gravatar image

=SUM(VLOOKUP(B3:Z3,'sheet 01'.$A$4:$I$60,4,0))
will work if you enter it using Ctrl+Shift+Enter PC key combination. The VLOOKUP function expects its first parameter to be a single value. It will accept an array of values if it is entered as an array formula.

answer from Ken over at ooForum

edit flag offensive delete link more

Question Tools

1 follower


Asked: 2013-09-25 16:05:52 +0100

Seen: 3,950 times

Last updated: Sep 25 '13