Since you are a little vague on exactly what is the desired result here is a
reference for you to look at. I beleive Example 9 at the bottom is close to
what you are trying to do but it is kinda hard to tell from your description.

http://www.xldynamic.com/source/xld.SUMPRODUCT.html
--
HTH...

Jim Thomlinson


"John Sutton" wrote:

XL 2002

I have a seriously complicated spreadsheet, consisting of multiple
worksheets that I am constantly updating. I would like to replace a
Lookup formula with something that will return a range of data values.

Currently, I have a formula that looks like this
=-Lookup("4020 AL",TrialBl!$A$1:$B$2109)-Lookup("4020
AR",TrialBl!$A$1:$B$2109)-Lookup("4020
AZ",TrialBl!$A$1:$B$2109)-Lookup("4020 CO",TrialBl!$A$1:$B$2109)

The real formula is really much larger than this. What I'd like to do
is be able to look in the data and return the sum of the return vector
who have a "4020" in the Lookup Vector

Any help greatly appreciated

John
KBS