XLOOKUP where the lookup value is a formula

Hi,

I'm trying to use XLOOKUP where the lookup value is a formula, e.g.,:

     A7 = Hospitality - P0461

     A8 = RIGHT(A7)

     XLOOKUP(A8, table1.some_column, table1.some_other_column)

the XLOOKUP returns #N/A

If I hard-code the value, e.g.,:

     A9 = P0461

     XLOOKUP(A9, table1.some_column, table1.some_other_column) I get the correct value back.

Is there some way to do this as the values in A7 & A8 are dynamic?

Thanks,

--sue