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]

Your file has certainly helped.

first we need 2 helper cells , so In sheet "History Check" cell H1 enter :
=CONCATENATE("master!","W",MATCH($E$2,Master!$A$3:$A$100,0)+2)

In sheet "History Check" cell I1 enter :
=CONCATENATE("master!","AB",MATCH($E$2,Master!$A$3:$A$100,0)+2)

then go to sheet "History Check" , cell H3 (under date) enter the formula and drag down:
=IFERROR(OFFSET(INDIRECT($H$1),0,RIGHT(TEXT((IF(COUNTA(INDIRECT($H$1):INDIRECT($I$1))-((ROW(A1)*2))>=0,COUNTA(INDIRECT($H$1):INDIRECT($I$1))-((ROW(A1)*2)),"q")),"00\/00\/00"),2)),"")

then go to sheet "History Check" , cell I3 (under Result) enter the formula and drag down :
=IFERROR(OFFSET(INDIRECT($H$1),0,RIGHT(TEXT((IF(COUNTA(INDIRECT($H$1):INDIRECT($I$1))+(ROW($A$1))-((ROW(A1)*2))>=0,COUNTA(INDIRECT($H$1):INDIRECT($I$1))+(ROW($A$1))-(ROW(A1)*2),"q")),"00\/00\/00"),2)),"")