Function to return the last value in a contiguous set of data
 

I'm wondering if there's a way to have Excel look for a specific value in a
column (e.g. lookup the word "Government" in column A) and then if it finds
that value, to search down to the end of contiguous data following the lookup
value and return the value of the cell on the last row in the third column.

Can this be done?? I feel like it should be possible, but my brain is about
fried tried to figure it out...

Function to return the last value in a contiguous set of data
 

On Tue, 12 May 2009 12:42:02 -0700, Deb
wrote:

I'm wondering if there's a way to have Excel look for a specific value in a
column (e.g. lookup the word "Government" in column A) and then if it finds
that value, to search down to the end of contiguous data following the lookup
value and return the value of the cell on the last row in the third column.

Can this be done?? I feel like it should be possible, but my brain is about
fried tried to figure it out...

Try this formula:

=INDEX(C1:C100,100-MAX(ISBLANK(OFFSET(A1,MATCH("Government",A1:A100,0 ),0,100))*(100-ROW(OFFSET(A1,MATCH("Government",A1:A100,0),0,100) )))-1)

Note: This is an array formula that has to be entered with
CTRL+SHIFT+ENTER rather than just ENTER.

Change the 100 in 7 places to fit the number of rows with data that
you have in columns A:C.

Hope this helps / Lars-Åke

Function to return the last value in a contiguous set of data
 

=LOOKUP(2,1/(A1:A100="Government"),C1:C100)


"Deb" wrote:

I'm wondering if there's a way to have Excel look for a specific value in a
column (e.g. lookup the word "Government" in column A) and then if it finds
that value, to search down to the end of contiguous data following the lookup
value and return the value of the cell on the last row in the third column.

Can this be done?? I feel like it should be possible, but my brain is about
fried tried to figure it out...

Function to return the last value in a contiguous set of data
 

Unfortunately neither of these solutions quite work because the set of
contiguous data isn't constant. The spreadsheet changes monthly and so the
data might be 100 rows one month, but then only 80 rows the next. In
addition, I need to repeat this several times. So for instance, I need to
find "Government" and then return the value from the end of the set of data
that "Government" is found in for column C. After that, I'll need it to find
"AAA" and then return the value from the end of the set of data that "AAA" is
found in for column C, etc.