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