VLOOKUP with multiple look-up values
 

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

--
Excel 2000, Windows 2003 over Citrix PS4

VLOOKUP with multiple look-up values
 

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)





--
Best Regards,

Luke M
"WembleyBear" wrote in message
...
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

--
Excel 2000, Windows 2003 over Citrix PS4

VLOOKUP with multiple look-up values
 

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.


--
Excel 2000, Windows 2003 over Citrix PS4


"Luke M" wrote:

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)





--
Best Regards,

Luke M
"WembleyBear" wrote in message
...
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

--
Excel 2000, Windows 2003 over Citrix PS4


.

VLOOKUP with multiple look-up values
 

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

--
Biff
Microsoft Excel MVP


"WembleyBear" wrote in message
...
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.


--
Excel 2000, Windows 2003 over Citrix PS4


"Luke M" wrote:

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)





--
Best Regards,

Luke M
"WembleyBear" wrote in message
...
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

--
Excel 2000, Windows 2003 over Citrix PS4


.