The following array formula will return the value in column D that
correspsonds to the last value of "a" in the range C4:C10. Adjust the
references to C4 and C4:C10 to the cells you have. If an "a" is not
found in C4:C10, the formual returns #N/A.


=IF(COUNTIF(C4:C10,"a")=0,NA(),OFFSET(C4,MAX(ROW(C 4:C10)*(C4:C10="a"))-ROW(C4),1,1,1))

This is an array formula, so you *must* press CTRL SHIFT ENTER rather
than just ENTER when you first enter the formula and whenever you edit
it later. If you do this properly, Excel will display the formula
enclosed in curly braces { }. The formula will not return the correct
result if you do not use CTRL SHIFT ENTER. See
http://www.cpearson.com/Excel/arrayformulas.aspx for lots more
information about array formulas.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Tue, 14 Oct 2008 12:31:00 -0700, Mrugesh Shah
wrote:

how do I look up the last instance and calculate based on last input,
disregarding the previous input.

=LOOKUP("x",B46:AF46,B1:AF1)+7

In other words, if "x" is inputted twice on consecutive rows, then calculate
based on the last entry.