We already did, twice.
In $AC.RK2 (and in RJ2, and who knows in how many other cells / columns), the second (or inner) xlookup uses a “Result array” argument that is a range ($Bonus.$D$199:$E$206), which means that either:
- you are using the wrong function; or,
- you are using wrong arguments; or,
- the function needs to be an array formula.
Quoting from Help content:
If Result Array is a range of cells, the XLOOKUP function must be entered as an array formula.
Considering that the xlookup function in question is not located on a column by itself (as it is an inner function which takes its result as an argument for a second xlookup), my guess is that whatever you are trying to retrieve with that inner xlookup is not what you are really obtaining.
So, for row 2, you at least get some numeric result for the outer xlookup, which you see displayed on the cell ($AC.RK2). The first row (row 2) gets you “some” numeric result, despite having the “#VALUE!” error as result of the inner xlookup. But then, for the next rows, you actually get an error, because the outer xlookup doesn’t know what to do with the failing result from the inner xlookup.
As for the confusing results when you change the position of some worksheets, that is probably because you are using relative references to some other worksheet instead of using absolute references (or that is my first guess). Having a reference such as “nameofworksheet.cell” is a relative reference to the worksheet, whereas “$nameofworksheet.cell” (note the “$”) is a generic example of an absolute reference to the worksheet (with a relative reference to the cell within it).
Moreover, using worksheet names such as “AC” would not be generally recommended, since it is too similar to referencing an entire column. Similar cases are “AL”, “Med”, “Pe”, “AL”, “Pro”, “Cla”, even when some of those use a mix of upper and lower case letters.
The sample file you presented here seems to have multiple relations between its worksheets. I do not know whether these relations can be simplified. Perhaps you could try (temporarily) using simpler layouts and separating the steps into multiple columns / rows, instead of using multiple levels of inner/outer functions within one cell / column. Some of the intermediate results might not be really needed (such as displaying a column that you do not really need), but once you have the actual results matching your expectations, then you can try to combine those simpler steps into more complex formulas, thus eliminating the intermediate steps. Using intermediate steps is not always possible, but it is a valid technique in order to find out what exactly does not match your expectations.
To be clear, a bug in some function is still a (hypothetical, generic) possibility; it just happens that your sample file does not help and does not prove such potential bug. It is more likely that you are misusing some function(s).
As already suggested, you need to completely review (or start from scratch, anew) the whole logic of your formulas, each step at a time, in order to find out where exactly you are misusing a function or an argument; meaning what exactly you actually obtain against what was that you were expecting to obtain. One row is not enough, as demonstrated by your own sample file.
Another humble sincere suggestion; you might want to spend some time reading and learning about the functions that you are using.
