# [solved] help with vlookup function [closed]

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

Sort by » oldest newest most voted

Code:
=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

more