INDEX, OFFSET, MAX and MATCH together?

[BrisAdrian]

This is what I have so far but it returns 6/01/1900 which I cant figure out:
=INDEX(Master!W:AZ,0,(MAX(W:AZ,(MATCH($E$2,Master!$A:$A,0)))))

So the in the sheet I'm working on, I want to use E2 as my reference, and return the last cell with data from the corresponding row on sheet2, starting from Column W up to AZ.

Sheet1:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]1[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]12345[/TD]
[TD]2/10/15[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][Nil][/TD]
[TD]5/8/15[/TD]
[/TR]
</tbody>[/TABLE]

Sheet2
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]1[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]W[/TD]
[TD]X[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]12345[/TD]
[TD]John Doe[/TD]
[TD]12/5/14[/TD]
[TD]5/8/15[/TD]
[TD]2/10/15[/TD]
[/TR]
</tbody>[/TABLE]


So that any time new data is entered in a persons data row, F2 on Sheet 1 will then display the last cell in that row, with F3 displaying the cell that is 1 to the left of the last cell. I should mention that the data is not going to be sequential ie: smallest to largest, it will have dates & general numbers & text. Which is why I want to go to the last cell, then go left 1, 2, 3cells etc.

Thanks for any help!
-Adrian

 

[admiral100]

Hi,

If i understand you well , F2 =last cell from sheet2 w:az , f3 = last cell -1

try this in F2 =INDEX(OFFSET(sheet2!$W$1,0,VALUE(RIGHT(TEXT(COUNTA(sheet2!$W$1:$AZ$1)-ROW(),"00\/00\/00"),2))),MATCH(גיליון1!$E$1,sheet2!$A$1:$A$100,0))

 

[admiral100]

F2 = INDEX(OFFSET(sheet2!$W$1,0,VALUE(RIGHT(TEXT(COUNTA(sheet2!$W$1:$AZ$1)-ROW(),"00\/00\/00"),2))),MATCH((sheet1!$E$1,sheet2!$A$1:$A$100,0))

 

[BrisAdrian]

Thanks for that mammoth formula Admiral, I'm getting an error message but it wont tell me where it is, I keep trying to change a little something here or there but I'm having no luck. I'll keep trying.

 

[Aladin Akyurek]

What is the problem: Get the last value for 12345 from Sheet2?

 

[BrisAdrian]

It comes up with an error box:
The formula you typed contains an error.
-For information about fixing common formula problems, click Help.
-To get assistance entering a function, flick Function Wizard (Formulas tab, Function Library group).
-If you are not trying to enter a formula, avoid using an equal sign (=) or minus sign (-), or precede it with a single quotation mark(').

This is the Formula with my Sheets updated:
INDEX(OFFSET(Master!$W$1,0,VALUE(RIGHT(TEXT(COUNTA(Master!$W$1:$AZ$1)-ROW(),"00\/00\/00"),2))),MATCH(('History Check'!$E$2,Master!$A$1:$A$100,0))
The Formula is entered on 'History Check' in H4 (random cell away from data where I am trying different formula's to make it happen).

This is the XLS if it helps: https://app.box.com/s/1po645hp5t25y2ixdk5suif5fs3nf4ov

 

[Aladin Akyurek]

What is the problem: Get the last value for 12345 from Sheet2?

In F2 of Sheet1 enter:

=LOOKUP(9.99999999999999E+307,INDEX(Sheet2!W:AZ,MATCH($E2,Sheet2!$A:$A,0),0))

Is this what you are looking for?

 

[BrisAdrian]

Thanks for the help Aladin.

This one is coming up as 'FALSE'

If the I turn the data in a table, will it have an impact?

 

[Aladin Akyurek]

In F2 of Sheet1 enter:

=LOOKUP(9.99999999999999E+307,INDEX(Sheet2!W:AZ,MATCH($E2,Sheet2!$A:$A,0),0))

Is this what you are looking for?

Thanks for the help Aladin.

This one is coming up as 'FALSE'

If the I turn the data in a table, will it have an impact?

Does your system use comma for the decimals and semi-colons between terms? If so:

=LOOKUP(9,99999999999999E+307;INDEX(Sheet2!W:AZ,MATCH($E2;Sheet2!$A:$A;0);0))

Note that the dates in Sheet2 must be true dates.

 

[BrisAdrian]

This is that I have in my data row on Sheet 2

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B ...[/TD]
[TD]... W[/TD]
[TD]X[/TD]
[TD]Y[/TD]
[TD]Z[/TD]
[TD]AA[/TD]
[TD]AB[/TD]
[TD]AC[/TD]
[TD]AD[/TD]
[TD]AE[/TD]
[TD]AF[/TD]
[TD]AG[/TD]
[TD]AH[/TD]
[/TR]
[TR]
[TD]Employee Number[/TD]
[TD]Employee Name[/TD]
[TD]10/11/2015[/TD]
[TD]6[/TD]
[TD]1/08/2015[/TD]
[TD]10[/TD]
[TD]18/8/2015[/TD]
[TD]8[/TD]
[TD]24/08/2015[/TD]
[TD]8[/TD]
[TD]11/09/2015[/TD]
[TD]7[/TD]
[TD]29/09/2015[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]


What I need to get out of it is:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Employee Number in Reference Cell on Sheet 1[/TD]
[/TR]
</tbody>[/TABLE]
Results on Sheet 1 (to be used to generate a graph per person on request)
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD]10/11/2015[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]1/08/2015[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]18/8/2015[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]24/08/2015[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]11/09/2015[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]29/09/2015[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]


The issue is that every other day people are getting more data added, so the most current wont be AG& AH, it might be AM and AN and so on.

Thank for all this effort Aladin, I am humbled by your kindness.