How do I shorten the following repetitive Lookup formula?

[Ngamia]

Hi everyone,

I have the following formula that retrieves the last value in a column B

=LOOKUP(2,1/(Quarterly_Sales!B:B<>""),Quarterly_Sales!B:B)

The values in column B are an alphanumeric sequence, SN2023-1234, and I need to extract the number after the dash - which I can do so using the formula below.

=RIGHT(A1,LEN(A1)-FIND("-",A1))

However when I now combine the two formulas by replacing A1 with the first formula, it becomes the unwieldy mess below.

=RIGHT(LOOKUP(2,1/(Quarterly_Sales!B:B<>""),Quarterly_Sales!B:B),LEN(LOOKUP(2,1/(Quarterly_Sales!B:B<>""),Quarterly_Sales!B:B))-FIND("-",LOOKUP(2,1/(Quarterly_Sales!B:B<>""),Quarterly_Sales!B:B)))

I am looking for a way to shorten this formula.

 

[Joe4]

You can use the LET function to assign the repeating part of your formula to a variable, so you can use that variable in your formula to shorten it up.
See: LET function - Microsoft Support

 

[Joe4]

I think your final formula using LET would look something like this:

=LET(x,LOOKUP(2,1/(Quarterly_Sales!B:B<>""),Quarterly_Sales!B:B),RIGHT(x,LEN(x)-FIND("-",x)))

 

[Fluff]

How about

=textafter(LOOKUP(2,1/(Quarterly_Sales!B:B<>""),Quarterly_Sales!B:B),"-")

 

[Alex Blakenburg]

Another option:

=IFERROR(TEXTAFTER(XLOOKUP(TRUE,Quarterly_Sales!B:B<>"",Quarterly_Sales!B:B,"",0,-1),"-"), "No Dash In Result")

Without the error handling

=TEXTAFTER(XLOOKUP(TRUE,Quarterly_Sales!B:B<>"",Quarterly_Sales!B:B,"",0,-1),"-")

 

[Ngamia]

Thanks all for your suggestions.

I just realised that I forgot to update my profile to indicate that I no longer have the MS-365 subscription.
The economic conditions finally made it impossible to keep it.

 

[Joe4]

You are welcome.
Glad we were able to help!