Forum Discussion
Compund XLOOKUP returns #VALUE error
=XLOOKUP(I$7,'[Sales Data by Month.xlsx]Sheet1'!$1:$1,'[Sales Data by Month.xlsx]Sheet1'!$2:$2)
=XLOOKUP($A8,'[Sales Data by Month.xlsx]Sheet1'!$A:$A,'[Sales Data by Month.xlsx]Sheet1'!$U:$U)
Both of the above work so why doesn't a compound XLOOKUP like below work? I get a #VALUE error on the second XLOOKUP.
=XLOOKUP($A8,'[Sales Data by Month.xlsx]Sheet1'!$A:$A,XLOOKUP(I$7,'[Sales Data by Month.xlsx]Sheet1'!$1:$1,'[Sales Data by Month.xlsx]Sheet1'!$R$2:$AF$64))
2 Replies
the ranges aren't the same size for the lookup and the return ranges:
=XLOOKUP($A8,'[Sales Data by Month.xlsx]Sheet1'!$A:$A,
XLOOKUP(I$7,'[Sales Data by Month.xlsx]Sheet1'!$1:$1,'[Sales Data by Month.xlsx]Sheet1'!$R$2:$AF$64))
so in the second XLOOKUP the lookup range is an ENTIRE row but the return range is only columns R:AF
and even if that worked it and it returned one of those columns it would only return rows 2:64 and the first XLOOKUP is looking up on a range of an ENTIRE column.
using INDEX and XMATCH would probably work:
=INDEX('[Sales Data by Month.xlsx]Sheet1'!$R$2:$AF$64,
XMATCH( $A8,'[Sales Data by Month.xlsx]Sheet1'!$A:$A),
XMATCH( I$7,'[Sales Data by Month.xlsx]Sheet1'!$1:$1) )
but if XMATCH doesn't find a value or finds a match outside the bounds of the R2:AF64 range counts that would be a problem and since you are starting at R2, would that XMATCH values be correct? e.g. A8 in A:A will be 8th row but index of 8 in that range would be the 9th row on the sheet.
Thank you. I have been using entire rows and/or entire columns as a crutch to avoid missing added lines or columns of data. I guess I will have modify the formula each time I add a new column of data.
Once I matched the first lookup to match the second lookup return array it worked.
=XLOOKUP($A8,'[Sales Data by Month.xlsx]Sheet1'!$A$2:$A$64,XLOOKUP(I$7,'[Sales Data by Month.xlsx]Sheet1'!$B$1:$AF$1,'[Sales Data by Month.xlsx]Sheet1'!$B$2:$AF$64))
