dynamic vlookup based on headers

[daveyc18]

so initiall i was using xlookup to get the last row of column BB, but now that specific column moved to column BH

the column header will always be "CAD EQUIV"

is there a way to have a dynamic lookup formula to get the last row of this column header?

 

[awoohaw]

Assuming your data is in cells BA2:BZ1000.
And your Headers are BA1:BZ1

=INDEX(BA2:BZ1000,999,MATCH("CAD EQUIV",BA1:BZ1,0))

 

[Alex Blakenburg]

Maybe something like this:

=LET(getCol, XLOOKUP(TRUE,(Sheet1!$A$1:$BZ$1="CAD EQUIV"),Sheet1!$A$1:$BZ$1000), XLOOKUP(TRUE,getCol<>0,getCol,"",0,-1))

OR

=LET(getCol, INDEX(Sheet1!$A$1:$BZ$1000,0,MATCH("CAD EQUIV",Sheet1!$A$1:$BZ$1,0)), XLOOKUP(TRUE,getCol<>0,getCol,"",0,-1))

 

[Cubist]

Maybe this.

=TAKE(FILTER(Sheet1!A1:BZ1000,Sheet1!A1:BZ1="CAD EQUIV"),-1)

 

[Alex Blakenburg]

Maybe this.

=TAKE(FILTER(Sheet1!A1:BZ1000,Sheet1!A1:BZ1="CAD EQUIV"),-1)

That will always return the last row in the range you specify ie in the range used by the formula 1:1000 it returns row 1000, so it is not dynamic.

 

[Cubist]

That will always return the last row in the range you specify ie in the range used by the formula 1:1000 it returns row 1000, so it is not dynamic.

You're right. Have to use XLOOKUP. I came up with something similar to yours.

=LET(f,FILTER(A1:BZ1000,(A1:BZ1="CAD EQUIV")),XLOOKUP(TRUE,f<>"",f,,0,-1))