XLOOKUP when lookup value may be text string or number formatted as text

[SallyC325]

I have a data set that has lookup value which may be text string or number formatted as text, with the lookup range formatted General.  How can I format the lookup value as general?  I currently have a formula with [if_not_found] as XLOOKUP value VALUE($A2), but would like to simplify the formula.

=IFS(
    $BA2 = "N",
    "",
    TRUE,
    CHOOSECOLS(
        XLOOKUP(
            $A2,
            'Data'!$A:$A,
            'Data'!$F:$AA,
            XLOOKUP(
                VALUE($A2),
                'Data'!$A:$A,
                'Data'!$F:$AA,
                ""
            )
        ),
        1,
        3,
        4,
        5,
        6,
        7,
        8,
        12,
        14,
        16,
        22
    )
)

Can I use TEXT formula to change lookup value to General format?  I saw another post that suggested the following but returns #VALUE! for numbers formatted as text :

=IFS(
    $BA2 = "N",
    "",
    TRUE,
    CHOOSECOLS(
            XLOOKUP(
                TEXT($A2,"General"),
                'Data'!$A:$A,
                'Data'!$F:$AA,
                ""
            )
        ),
        1,
        3,
        4,
        5,
        6,
        7,
        8,
        12,
        14,
        16,
        22
    )
)

  How can I fix this?

[#2]

@SallyC325 

With lookup formulas, it's a matter of is the value text or a number.  Also, the result of TEXT is going to be text.

XLOOKUP forcing the lookup value to be read as a number:

=XLOOKUP(A2*1,lookup,return)

XLOOKUP forcing a number to be treated as text:

=XLOOKUP(TEXT(A2,"0"),lookup,return)

[#3]

@Patrick2788Thanks for your response.  This returns #VALUE for Text strings (e.g. CBYRH200020) when forcing XLOOKUP to be read as a number so still required an [if_not_found] alternative, if I have understood you correctly? 

The lookup range has General format, so numbers look like numbers and text looks like text.  Is there a way to force same formatting on the lookup value?  I have tried to find the code for "General" format ... and failed.

[#4]

@SallyC325 

You could use:

=XLOOKUP(IFERROR(A2*1,A2),lookup,return)