Showing the last value in a column in a named table?

[Smokeyham]

Hello,

I was thinking this would be quite simple to do, but after a couple of hours of research I still have not seemed to solve it.

I am trying to show the last value in a specified column from a named table. The result will be in a new table which has transposed the column headings into row headings.

The source data is in a table named "CurrentNW". As an example, one column in the source data is labelled "CapitalOne." New row entries start with a date in left hand column (don't know if that is significant or not).

I have tried Index, Xlookup, and Take - All without success.

I am using Office 365.

Thanks for any help.
Gordon



Microsoft® Excel® for Microsoft 365 MSO (Version 2309 Build 16.0.16827.20166) 32-bit

 

[PeteWright]

Hello @Smokeyham could you provide a sample? That would make things much easier for me.
A screenshot of a table (filled manually) that shows the source and result data.

 

[kevin9999]

Not sure what you'll be doing with the value once you've got it, but here's both a formula & VBA possibility for you to consider:

Book1
	A	B	C	D	E	F	G	H
1
2		HDR1	HDR2	HDR3	CapitalOne	HDR5	HDR6
3		1	data	data	data	1	data
4		2	data	data	data	1	data
5		1	data	data	data	1	data
6		2	data	data	data	1	data
7		1	data	data	This item	1	data
8
9		This item
10
Sheet1

Cell Formulas
Range	Formula
B9	B9	=INDEX(CurrentNW,COUNTA(CurrentNW[[#All],[HDR1]])-1,MATCH("CapitalOne",CurrentNW[#Headers],0))

Sub Test()
    Dim tbl As ListObject, rng As Range
    Set tbl = ActiveSheet.ListObjects("CurrentNW")
    Set rng = tbl.DataBodyRange(tbl.DataBodyRange.Rows.Count, _
    Application.Match("CapitalOne", tbl.HeaderRowRange, 0))
    
    MsgBox rng.Value
    rng.Select
End Sub

 

[Alex Blakenburg]

Since you mentioned trying XLookup, this should work:

=XLOOKUP(TRUE,CurrentNW[CapitalOne]<>"",CurrentNW[CapitalOne],,,-1)

 

[RoryA]

The source data is in a table named "CurrentNW". As an example, one column in the source data is labelled "CapitalOne." New row entries start with a date in left hand column (don't know if that is significant or not).

I have tried Index, Xlookup, and Take - All without success.

What happens with:

=TAKE(CurrentNW[CapitalOne],-1)