Help with formula - variant of last row problem/s

[henryg]

I have a lookup list (but not a table!) with names vertically, and a data table with names (that should be?) in the lookup list horizontally, eg

Lookup List
V
W
X
Y
Z

Table columns (MnthData)
A, V, W, X, Y, Z, where A contains dates in ascending order. The column order may not be the same as the list.

I want to lookup a name in the lookup list eg Z and return the latest value from (last row of) the data table where the header is "Z".

Bearing in mind I have attempted to simplify the example and my formula below, not a bad thing, I end up with

=XLOOKUP($V$9:$V$13,MnthData[[#Headers],{B]:[F]],INDIRECT("B"&COUNT(MnthData[Date])+8):INDIRECT("F"&COUNT(MnthData[Date])+8),"")

+8 because that's where the data in the MnthData data table starts.

This works (at least in my full version of the formula), but I really dislike using INDIRECT(). So I'm looking for a better way of doing it.

I may have messed up simplifying the formula but hope you get the idea.

 

[FormR]

Hi, not if I've understood correctly, but maybe something like this:

Book3
	A	B	C	D	E	F	G	H	I
1
2
3
4
5
6
7	A	X	V	Y	Z	W		Lookup List	Last Value
8	01-Jan	237	5	778	717	341		V	7
9	02-Jan	168	4	599	212	215		W	766
10	03-Jan	742	6	329	367	732		X	280
11	04-Jan	751	7	258	671	896		Z	143
12	05-Jan	398	8	821	413	610
13	06-Jan	438	9	84	441	847
14	07-Jan	280	7	585	143	766
Sheet1

Cell Formulas
Range	Formula
I8:I11	I8	=TAKE(INDEX(MnthData,0,MATCH(H8,MnthData[#Headers],0)),-1)

 

[henryg]

I've always had problems using index/match, so jump to XLOOKUP(). I did have an index/match version but it could not cope when the table headers order was not the same as the lookup list. I'll give this a try.

And you've used TAKE() which I've really wanted to use

Thank you.

 

[RoryA]

You could also use:

=XLOOKUP($V$9:$V$13,MnthData[#Headers],INDEX(MnthData,COUNT(MnthData[Date]),0))

if you didn't have TAKE available yet.

 

[henryg]

You could also use:

=XLOOKUP($V$9:$V$13,MnthData[#Headers],INDEX(MnthData,COUNT(MnthData[Date]),0))

if you didn't have TAKE available yet.

I'll try both

 

[henryg]

After trying both of the above solutions, and breaking down each component into separate cells, I ended up with

=XLOOKUP(V9:V21,MnthData[#Headers],TAKE(MnthData,-1))

which gives what I wanted without INDIRECT; and is much clearer than my (full) original formula.

Thank you both for the help. Much better than ChatGPT