Return cell reference instead of text from within lookup() functio
 

Hi,

I'm trying to use the following formula to find data for a particular day of
the month and then sum() a 7 day range of data for that week.

=SUM(OFFSET(LOOKUP('Dashboard (2)'!D16,'Seat Cap Daily'!$B6:$CP6,'Seat Cap
Daily'!$B32:$CP32),0,-6,1,7))

I think the problem is that offset() is expecting a cell reference, but
lookup() is returning cell content. How can I search and locate my expected
content and pass that content's cell reference to lookup().

Thanks,
Earl

Return cell reference instead of text from within lookup() functio
 

Last line should read:

How can I search and locate my expected
content and pass that content's cell reference to offset().

"earls" wrote:

Hi,

I'm trying to use the following formula to find data for a particular day of
the month and then sum() a 7 day range of data for that week.

=SUM(OFFSET(LOOKUP('Dashboard (2)'!D16,'Seat Cap Daily'!$B6:$CP6,'Seat Cap
Daily'!$B32:$CP32),0,-6,1,7))

I think the problem is that offset() is expecting a cell reference, but
lookup() is returning cell content. How can I search and locate my expected
content and pass that content's cell reference to lookup().

Thanks,
Earl

Return cell reference instead of text from within lookup() functio
 

Try

=SUM(OFFSET(INDEX('Seat Cap
Daily'!$B32:$CP32,MATCH('Dashboard (2)'!D16,'Seat Cap
Daily'!$B6:$CP6,0)),0,-6,1,7))

This gives an exact MATCH only, if you're looking for the nearest match in a
sorted range then change the first zero (the third argument of MATCH) to a 1

"earls" wrote:

Hi,

I'm trying to use the following formula to find data for a particular day of
the month and then sum() a 7 day range of data for that week.

=SUM(OFFSET(LOOKUP('Dashboard (2)'!D16,'Seat Cap Daily'!$B6:$CP6,'Seat Cap
Daily'!$B32:$CP32),0,-6,1,7))

I think the problem is that offset() is expecting a cell reference, but
lookup() is returning cell content. How can I search and locate my expected
content and pass that content's cell reference to lookup().

Thanks,
Earl

Return cell reference instead of text from within lookup() fun
 

Works as you describe. Thanks a bunch for your help...

"daddylonglegs" wrote:

Try

=SUM(OFFSET(INDEX('Seat Cap
Daily'!$B32:$CP32,MATCH('Dashboard (2)'!D16,'Seat Cap
Daily'!$B6:$CP6,0)),0,-6,1,7))

This gives an exact MATCH only, if you're looking for the nearest match in a
sorted range then change the first zero (the third argument of MATCH) to a 1

"earls" wrote:

Hi,

I'm trying to use the following formula to find data for a particular day of
the month and then sum() a 7 day range of data for that week.

=SUM(OFFSET(LOOKUP('Dashboard (2)'!D16,'Seat Cap Daily'!$B6:$CP6,'Seat Cap
Daily'!$B32:$CP32),0,-6,1,7))

I think the problem is that offset() is expecting a cell reference, but
lookup() is returning cell content. How can I search and locate my expected
content and pass that content's cell reference to lookup().

Thanks,
Earl