XLOOKUP issue returning an array

Welcome to the forum.

Check that your numbers are real numbers and not text.

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. The sample layout accurately matches that of your real data. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

Depends, maybe, on how cell A1 is/was formatted, what you manually typed in, and what's in B1. Guessing that if you had text in B1 and you tried to subtract 1, you'd get #VALUE! On the other hand, if the lookup range is text, and the search value was text (A1) it should work. But, if the lookup range is text, and B1-1 produces a numeric value, it won't work even if it looks as though it should.

Starting in a new book, all the cells would be formatted as General, so that might make a difference.

We need to see the book that demonstrates the issue for a definitive answer.