Index function

[ExcelAlice]

I am using the index function, expecting that if I refer to "494" it will return the data from the 494th row of my data, in the column that i reference - however it returns data from 1 cell below. Can you tell me why?

 

[lenze]

Hi and welcome to the board!!
Can you explain exactly what you are trying to do? Also, please post your current formula.
lenze

 

[Ron Coderre]

Can you post your formula?

 

[ExcelAlice]

<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=64 border=0><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=64 height=20>=INDEX(2009_10_Daily Tracker XOM.xls'!Data_1570,'Service Level Calculator'!$D6,'Service Level Calculator'!E$5)</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>where $D6 is the # that represents 1 less than the row of data I want to reference</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>and E$5 is the column of data I want to reference</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>question is, why does the row reference have to, for instance, 75 to get it to pull the data in 76?</TD></TR></TBODY></TABLE>

 

[shemayisroel]

Hi,

Not sure what you are trying to do but maybe the below may help youy in regards to the INDEX function.

http://www.contextures.com/xlFunctions03.html

 

[shemayisroel]

I'd also reccomend you use the ROWS or COLUMNS functions within your formula rather than referencing it to another cell.

If you care to post some sample data with expected result(s) I'm sure some one will be able to offer you a elegant solution(s)