Formula to return a cell contents of the last row of a table

[ExcelFailure]

Good Afternoon,

I'm after a formula which I think (and hope) is quite simple:

It just needs to return the text contents of a cell that is in the last row of a Query table. The query updates every minute so the table could and will get bigger. I'm after the 3rd cell in from the left, on the last row of the table.

Hope you understand

Thanks

[Jonmo1]

Clarify....

Do you want the value of the absolute last row of the table?
Or do you want the last value that appears in the table (not necessarily in the last row of the table)
I.E.
If there are blanks at the bottom of the table

A1 = x
A2 = y
A3 = empty

do you want the empty or the y ?

[ExcelFailure]

I want the y but there won't be any blank rows. Or there certainly shouldn't be

[Jonmo1]

Try

=LOOKUP(REPT("Z",255),A1:A100)

[FDibbins]

you can probably use counta() to find the last non-blank row, and then build that into an index/match using indirect()

[ExcelFailure]

Do I replace the A1:A100 with the query?

[Jonmo1]

Replace A1:A100 with whatever represents the column you want to find the last text value in.

Here's an example using a basic table

=LOOKUP(REPT("z",255),TableName[HeaderName])

[ExcelFailure]

Thanks that works, although I have another small problem. When making the formula, and I select the relevant table column by using the small black arrow just above the table header, it inserts "Sheet1!C1:C40", which is the space that that row of the table occupies currently. However when the query expands adding new data, the selection stays at "Sheet1!C1:C40", so it doesn't include the new data. What I really need is to be to get it to say "TableName[Header Name], so that the selection expands along with the table.

Any ideas? Thanks for the help so far

[Jonmo1]

Is the table 'really' a table?

Select any cell within the 'table'
Go the Insert Tab, and insert table.

[ExcelFailure]

Yep the table button is greyed out. And a tab up the top appears with 'Design' on it

[Jonmo1]

What happens if you manually type the tablename[headername] ?
Does it stay, or get replaced by the range reference?

[ExcelFailure]

Sorry, just found out - I think it's only the LOOKUP formula that it doesn't like. I just tried a VLOOKUP and it was fine.

[Jonmo1]

That's odd..
But as long as you have solution

[ExcelFailure]

But I don't............the VLOOKUP was just a dummy formula to see if it accepted the right table reference

[Jonmo1]

Can you attach your file?
cleaned of personal info, and reduced in size, we only need 10 or 20 rows of data to work with.

[ExcelFailure]

For some unexplained reason it now works..........thanks very much..have some rep

[Jonmo1]

You're welcome, thanks for the feedback.