Cell value

[AlGuy]

I am trying to get the value of a cell based on the last value of a row that used the lookup formula.

Let's say in cell A1, I referenced the last value of the row 300 on the sheet 2 renamed Notes. I used the Lookup function:
A1 value: (=LOOKUP(9.99999999999999E+307,Notes!300:300). The value in A1 equals to the year 2010 value. Its cell ref is M300.

In the cell A2, I want to see the value of the year 2009, which is located on the same sheet (Notes). The cell ref is L300. But instead of calling the cell by typing =Notes!L300, I want excel to find the value of cell A2, based on that of cell A1. In other words, if the last value of the row 300 in 10 years is that of 2020, excel will put in the cell A2, the value of year 2019 automatically.

Thank you for the help.

 

[Snakehips]

AlGuy,

Does this help......


With a date in Notes row 300
Sheet3

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: right">2012</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right">2011</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>A1</TD><TD>=YEAR(LOOKUP(9.99999999999999E+307,Notes!300:300))</TD></TR><TR><TD>A2</TD><TD>=A1-1</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

 

[AlGuy]

Snakehips,

Thank you. No it didn't work. It just take the value of the cell A1 (2012 for example) minus 1 (2011).
Let's use the data in your example. I don't know how to insert images or build cells like you on the forum. Arrange the first table so that years are in a row and below that row, you have values:
Column A B ...
Row1:2011 2012 ...
Row 2: 5,000 6,000 ...
or
A1=2011 A2=5,000
B1=2012 B2=6,000
What your formula does it taking a year value corresponding to 5,000, minus 1 and the result is some year value.
What I would like to get is a formula to display the last value of the row 2 (the lookup function works fine for that). So on the formula cell, the displayed result is 6,000. On the next cell, I would like to have another formula to recall the value of B2, based on the previous formula.
That way, when new data are added to the table, the results are updated automatically.

 

[circledchicken]

Hi,

Do you mean something like this:

=INDEX(Notes!300:300,LOOKUP(9.99999999999999E+307,Notes!300:300,COLUMN(Notes!300:300)-1))

 

[AlGuy]

circledchicken,

You guys are wonderful!!!! It works!!!! Thank you. Exactly what I wanted!!!!!!!
So GREAT!!!!!!!

 

[circledchicken]

Your welcome! and thanks for the feedback.

 

[T. Valko]

Hi,

Do you mean something like this:

=INDEX(Notes!300:300,LOOKUP(9.99999999999999E+307,Notes!300:300,COLUMN(Notes!300:300)-1))

This does the same thing and saves a few keystrokes.

=INDEX(Notes!300:300,MATCH(1E100,Notes!300:300)-1)

 

[circledchicken]

This does the same thing and saves a few keystrokes.

=INDEX(Notes!300:300,MATCH(1E100,Notes!300:300)-1)

How do you always come up with better solutions than mine Nice one !

 

[T. Valko]

How do you always come up with better solutions than mine Nice one !

You did the heavy lifting!

 

[Aladin Akyurek]

I am trying to get the value of a cell based on the last value of a row that used the lookup formula.

Let's say in cell A1, I referenced the last value of the row 300 on the sheet 2 renamed Notes. I used the Lookup function:
A1 value: (=LOOKUP(9.99999999999999E+307,Notes!300:300). The value in A1 equals to the year 2010 value. Its cell ref is M300.

In the cell A2, I want to see the value of the year 2009, which is located on the same sheet (Notes). The cell ref is L300. But instead of calling the cell by typing =Notes!L300, I want excel to find the value of cell A2, based on that of cell A1. In other words, if the last value of the row 300 in 10 years is that of 2020, excel will put in the cell A2, the value of year 2019 automatically.

Thank you for the help.

circledchicken,

You guys are wonderful!!!! It works!!!! Thank you. Exactly what I wanted!!!!!!!
So GREAT!!!!!!!

Less risky...

=LOOKUP(9.99999999999999E+307,INDEX(Notes!300:300,MATCH(9.99999999999999E+307,Notes!300:300)-1))