I'm using a XLOOKUP formula in a set of formulas and values that are part of the LET(...) function. I'm trying to get matching results for each row in a matrix which are searched in a second table. I believe the problem is that the formula doesn't do the match on a row by row basis, but tries to match all values at the same time.
In other words what I'm trying to do here, is "for each row in the column of the matrix, find the corresponding value in table TAA" Only one value should match in the TAA table based on the used criteria.
I tried to get the results by using the FILTER formula, but this also caused the #VALUE! error. In buth attempts, when I'm using a fixed value for each criteria, it works without errors (for example
(TAA[taa_year]=2023)*(TAA[taa_month]=3)
I enclosed the sample sheet. The results are used as source for a graph in Excel.
How can I get this working?
Art.
Bookmarks