VLOOKUP with multiple look-up values

[WembleyBear]

I have a vlookup which looks up the value of an expense code for a particular
branch, set up as a named range (BRANCH A) & chosen from a drop-down list in
cell C3. It works fine for one expense code 4001 thus:

=VLOOKUP(4001,INDIRECT(C3),2,FALSE)

My problem comes when in some instances I need to add together the value of
several (up to 10) expense codes in that cell, say 4001,4003,4011,4014). The
total value would then change dependent on which branch is selected, but the
group of expense codes would always be the same irrespective of which branch
is chosen in C3. What is the easiest way of achieving this?


Thanks
Martyn

 

[Luke M]

Easiest? Probably just:
=VLOOKUP(4001,INDIRECT(C3),2,FALSE)+
VLOOKUP(4003,INDIRECT(C3),2,FALSE)+
VLOOKUP(4011,INDIRECT(C3),2,FALSE)+
VLOOKUP(4014,INDIRECT(C3),2,FALSE)

 

[WembleyBear]

Yes, I had thought of that but several of the groups of codes would produce a
formula that is too long if I used that method.

 

[T. Valko]

Not sure I understand your layout...

BRANCH A is not a valid range name. Can't include spaces.

If you have a lookup table like this named BRANCH_A:

4000...10
4001...22
4002...17
4003...15

And the table is sorted in ascending like above *and* there will always be
an exact match of the lookup values, then try something like this:

A1 = 4001
A2 = 4003

=SUMPRODUCT(LOOKUP(A1:A2,INDIRECT(C3)))

Result = 37