Add TRIM around the ranges in the XLOOKUP:

=XLOOKUP (B34,TRIM(Sheet2!D14:D17),TRIM(Sheet2!D14:D17),)

The TRIM, removes extra spaces in a string, but leaving 1 only and not more than one, and removes extra spaces to zero spaces at the beginning and end of a string, a few database errors resolved by one formula.

As names can be the same for different customers, or entered many times, you may need to add another bit of data to be sure you have "unique" names. I use COUNTIFS all the time, and doing a COUNTIF first for the range of names (lock the column letters and numbers with "$"), in both databases to see if you only get "1" for each name: =COUNTIF($B$32:$B$34,B34)

if not you will need a COUNTIFS, to maybe add the postcode (can be isolated with RegEx) to be counted with the name.