Last value in column

[BuffaloGuy]

I have a table on sheet "History" called Table 6, and a column in that table called Item #.

In cell B2 on sheet "Initial", I want to have the value of the last non-blank in the item # column.

currently I have =XLOOKUP(2,1/(Table6[@[Item '#]]<>0),Table6[@[Item '#]],"N/A",-1,-1)

it brings up the first value in the table

 

[Cubist]

Try:

=XLOOKUP(TRUE,Table6[Item '#]<>"",Table6[Item '#],,,-1)

 

[Dave Patton]

an alternative

BigNum is a named value a very large number =9.99999999999999E+307

T202501a.xlsm
	A	B	C	D
1	BBBB	Item #
2	a 2	1		3
3	c 4	2
4	x 5
5	k 7	3
6	AA
4b

Cell Formulas
Range	Formula
D2	D2	=LOOKUP(BigNum,Table6[Item '#])

 

[Peter_SSs]

currently I have =XLOOKUP(2,1/(Table6[@[Item '#]]<>0),Table6[@[Item '#]],"N/A",-1,-1)

Don't you just need to take the @ signs out of that formula?

=XLOOKUP(2,1/(Table6[Item '#]<>0),Table6[Item '#],"N/A",-1,-1)

A couple of other options

=LET(d,Table6[Item '#],f,FILTER(d,d<>"","N/A"),INDEX(f,ROWS(f)))

=LET(d,Table6[Item '#],XLOOKUP("?*",d&"",d,"N/A",2,-1))

 

[Dave Patton]

Edit to my post. It is very early in the morning here.

I read value as a number. It is possible to use this approach with text but you have solutions.

Have a good day.

 

[BuffaloGuy]

Don't you just need to take the @ signs out of that formula?

=XLOOKUP(2,1/(Table6[Item '#]<>0),Table6[Item '#],"N/A",-1,-1)

A couple of other options

=LET(d,Table6[Item '#],f,FILTER(d,d<>"","N/A"),INDEX(f,ROWS(f)))

=LET(d,Table6[Item '#],XLOOKUP("?*",d&"",d,"N/A",2,-1))

All I had to do was take out the @ like you said. I had borrowed that formula from somewhere else and didn't realize they shouldnt have been there.

Thank you!

 

[Peter_SSs]

You're welcome. Glad to help. Thanks for the follow-up.
As you can see, there are several ways of achieving the result.

 

[BuffaloGuy]

Don't you just need to take the @ signs out of that formula?

=XLOOKUP(2,1/(Table6[Item '#]<>0),Table6[Item '#],"N/A",-1,-1)

A couple of other options

=LET(d,Table6[Item '#],f,FILTER(d,d<>"","N/A"),INDEX(f,ROWS(f)))

=LET(d,Table6[Item '#],XLOOKUP("?*",d&"",d,"N/A",2,-1))

The previous answer worked, but not exactly for what I ended up needing in the next phase, but

=LET(d,Table6[Item '#],XLOOKUP("?*",d&"",d,"N/A",2,-1))

was exactly what i needed.

 

[Alex Blakenburg]

Did you try @Cubist's suggestion in Post #2 ?
You just needed to add the "N/A" back into the Not Found position.

 

[BuffaloGuy]

Did you try @Cubist's suggestion in Post #2 ?
You just needed to add the "N/A" back into the Not Found position.

yes, both solutions worked.