Sheet lookup

[Andrew Clark]

Hello,

I have values that I would like to look up on different sheets rather
than the same one, so I can't use vlooup(). So far I have been defining
named ranges, but my formula is getting too long. Here is what I do:

SUM(IF(ISERROR(VLOOKUP($A3,mar_03_batting,COLUMN(C$1)-1,FALSE)),0,VLOOKUP
($A3,mar_03_batting,COLUMN(C$1)-1,FALSE)),...

Basically, see if the name can be found on the sheet, and if it can, add
the vlookup'ed value in. On another note, why is vlookup() designed to
return an error rather than a number?? That's my number 1 hated
"feature" of excel.

Thanks,
Andrew

 

[EML]

Why do you think you can't use VLOOKUP to find data on another sheet?

 

[Bob Phillips]

VLOOKUP is fine over another sheet.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Andrew Clark]

VLOOKUP is fine over another sheet.

Yes, sorry. The problem I have is that to I will have many sheets like
this and I need to include them all in the expression. When I put all the
vlookups in the sum, the length exceeds the formula length limit. I have
this expression:

IF(ISERROR(VLOOKUP($A3,mar_03_batting,COLUMN(C$1)-1,FALSE)),0,VLOOKUP
($A3,mar_03_batting,COLUMN(C$1)-1,FALSE))

repeated for each day, substituting _day_ for _03_. I was just lokoing
for a way to simplify the above expression so I could include all of the
days in one formula. As it is now, I can include 9 such expressions, then
the formula becomes too long.

Thanks,
Andrew

 

[Bob Phillips]

Either put the repeated formulas, such as

VLOOKUP($A3,mar_03_batting,COLUMN(C$1)-1,FALSE)

in another cell and refer to that, or create a named range referring to that
and use the name in the final formula.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Harlan Grove]

Andrew Clark wrote...

I have values that I would like to look up on different sheets rather
than the same one, so I can't use vlooup(). So far I have been defining
named ranges, but my formula is getting too long. Here is what I do:

SUM(IF(ISERROR(VLOOKUP($A3,mar_03_batting,COLUMN(C$1)-1,FALSE)),0,VLOOKUP
($A3,mar_03_batting,COLUMN(C$1)-1,FALSE)),...

Basically, see if the name can be found on the sheet, and if it can, add
the vlookup'ed value in. On another note, why is vlookup() designed to

return an error rather than a number?? That's my number 1 hated
"feature" of excel.

If the name in A3 would appear at most once in the first column of
mar_03_batting and all other such ranges, don't screw around with
VLOOKUP. Enter all these table ranges in another range, easier to
maintain if this is a single column, multiple row range, and name the
*transpose* of this range of range names something like ListOfTables.
Then try the array formula

=SUM(SUMIF(OFFSET(INDIRECT(ListOfTables),0,0,,1),$A3,
OFFSET(INDIRECT(ListOfTables),0,COLUMN(C$1)-2,,1)))