Find the last letter in a column and return a value in the same row but a different column

I would like to create a formula which returns a value in the same row as the last letter in a column (column A). An example of the data is shown below. In this case the last "G" is found in cell A6. I would like to return the corresponding "End" value (column E), in this case "0.1".

Good RefDist Dist Start End
3.323 0 0 0.02
G 3.343 0.02 0.02 0.04
G 3.363 0.04 0.04 0.06
G 3.383 0.06 0.06 0.08
G 3.403 0.08 0.08 0.1
3.423 0.1 0.1 0.12
3.443 0.12 0.12 0.127

The data will be in a series of other worksheets (each with a different number of rows of data and a different number of rows with "G"' but the same number of columns as shown in the example). There will be one reporting worksheet so I will require a reference to the other worksheets - I am thinking perhaps an indirect function could do this. The reporting worksheet will have a list of the different data worksheets.

I thought the formula in the reporting worksheet would start something like:
=INDIRECT($C1&"!E"& and so on ...

where $C1 refers to the name of a data worksheet
and E refers to the column in the data worksheet with the "End" data in it
I was thinking the formula may end with means to refer to the last row with a "G" in it (maybe a ROW function combined with a LOOKUP function).

Any help would be appreciated thanks

Is it specifically a G you're looking for the last occurance of?
Or just ANY textual value ?

Try

=LOOKUP(REPT("z",255),A1:E100)

Example Data.xlsx


Better example of data attached

"G" will be the only text in Column A - it just indicates what is the good data

If column A will only be either G or Empty, then my suggestion can be simplified a bit..

=LOOKUP("z",A1:E100)

If you want to get the value from column E on the row where the last G occurs, you can use this array* formula - put it in a blank cell somewhere (e.g. H1):

=INDEX(E:E,MAX(IF(A2:A8="G",ROW(A2:A8))))

*An array formula needs to be committed using the key combination of Ctrl-Shift-Enter (CSE) instead of the usual <Enter>

Hope this helps.

Pete

Both of the formula work fine when they are in the same sheet as the data, thanks, but can you please tell me how to combine with an indirect function? If the formula was in Sheet 1 and the data was in Sheet 2, and in Sheet 1 cell C1 contained the name "Sheet 2" how would you combine them? Thanks

to use indirect, try

=LOOKUP("z",INDIRECT("'"&C1&"'!A1:E100"))

I'd leave the formula where it is (i.e. in H1 on Sheet 2) and then use this formula in Sheet1:

=INDIRECT("'"&C1&"'!H1")

Then you could have a similar arrangement with other sheets and just copy the formula down.

Hope this helps.

Pete