XLOOKUP or FILTER matrix result expected but #VALUE! returned

[dxfoxd]

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

Please Login or Register  to view this content.

I enclosed the sample sheet. The results are used as source for a graph in Excel.

How can I get this working?

Art.

[HansDouwe]

XLOOPUP causes #VALUE because the lookup array consists of 67 elements and the return array has only 24 elements.
This must be equal.

When you truncate the lookup matrix to the size of the return matrix, #VALUE no longer occurs, but normal values ​​are returned.

Please try:

Please Login or Register  to view this content.

[dxfoxd]

Thx Hans! I would never come up with this, or at least not within a week or so :-)

[HansDouwe]

You are Welcome!

Thanks for the feedback and rep.

Glad to have helped .

[dxfoxd]

While applying this solution to the actual dataset the problem returned, but I cannot discover why this isn't working. the TAKE/ROWS make sure that the XLOOKUP formula is limited to the number of rows of the second table. I updated the sample, where the number of records are increased and only 2 columns are added to be used for the filter criteria. However, these filters are not used in the XLOOKUP formula, but the result of XLOOKUP is again #VALUE!. What am I missing here?

[HansDouwe]

There is no 1 in ((TAA[taa_year]=CHOOSECOLS(flt,1))*(TAA[taa_month]=CHOOSECOLS(flt,2)) (only zeros), so the XLOOKUP can't find anything.

[dxfoxd]

What do you mean? The CHOOSECOLS(flt,1) lists all years and CHOOSECOLS(flt,2) list all months that are in the filtered dataset flt.

[dxfoxd]

Actually it is even more complicated. The first solution did have results, but these were incorrect. XLOOKUP did only show one value which was not the right value based on the criteria.

The new problem is now, if I add 2 colums to the PERF table and use the same filter as used in the first sample sheet, I get a result of >7000 rows and the XLOOOKUP shows a simular result as the first solution (which is incorrect.
However, if I add 2 criteria to the filter, I get a result of >1800 and then the #VALUE! error is back again.

Besides that the first solution isnt working after all, I cannot understand why only the number of rows are affecting the XLOOKUP as they have the same number of columns and similar values as the first FILTER.

In the attached spreadsheet I inserted both formulas side by side and as a last table I inderted an standalone XLOOKUP and the expected calculations. These work, but I prefer to include the XLOOKUP it in the LET formula.

The question is still, how to accomplish this?

Art

[HansDouwe]

\The first solution did have results, but these were incorrect

The problem is that you indicate that the formula produces a #VALUE, but you do not indicate what the expected results are and how these results should be calculated. So I can only point out what causes #VALUE. And I did that in both cases.

[dxfoxd]

Compared to the first sheet, I added the s_corr and d_corr just to show when and where it goes wrong.
The attached sheet shows 2 sets of formulas. The first one is the extended output based on the first fsolution. The second set is the formula when adding filters to the first matrix but without toutching the XLOOKUP formula. The third set shows what the output is expected based on the formula used in the second set.
I really appreciate your help in this!

[HansDouwe]

This formula returns the expected results:

=LET( 
source,PERF, 
y,$B$1, 
m,$B$2, 
old_flt,CHOOSECOLS(FILTER(source,(PERF[perf_year]=y-1)+((PERF[perf_year]=y)*(PERF[perf_month]<=m)),"N/A"),1,2,5,6), 
flt,CHOOSECOLS(FILTER(source,(PERF[code]=$B$4)*(PERF[type]=$B$3)*((PERF[perf_year]=y-1)+((PERF[perf_year]=y)*(PERF[perf_month]<=m))),"N/A"),1,2,5,6), 
label,CHOOSECOLS(flt,1,2), 
data,CHOOSECOLS(flt,3,4), 
grp,GROUPBY(label,data,SUM,0,0), 
y_g,CHOOSECOLS(grp,1), 
m_g,CHOOSECOLS(grp,2), 
ym_t,CHOOSECOLS(TAA,1)&CHOOSECOLS(TAA,2), 
s_corr,MAP(y_g&m_g,LAMBDA(ym,XLOOKUP(ym,ym_t,CHOOSECOLS(TAA,3)))), 
s_new,CHOOSECOLS(grp,3)*(1+s_corr), 
d_corr,MAP(y_g&m_g,LAMBDA(ym,XLOOKUP(ym,ym_t,CHOOSECOLS(TAA,4)))), 
d_new,CHOOSECOLS(grp,4)*(1+d_corr), 
HSTACK(grp,s_new,d_new))  

[dxfoxd]

Yes!!! Thx Hans, you're a legend! I already thought on a similar FOR EACH instruction in VB but I couldn't find an equivalent formula for Excel. MAP...LAMBDA it is!

[HansDouwe]

It is also possible without MAP LAMBDA:

=LET( 
source,PERF, 
y,$B$1, 
m,$B$2, 
old_flt,CHOOSECOLS(FILTER(source,(PERF[perf_year]=y-1)+((PERF[perf_year]=y)*(PERF[perf_month]<=m)),"N/A"),1,2,5,6), 
flt,CHOOSECOLS(FILTER(source,(PERF[code]=$B$4)*(PERF[type]=$B$3)*((PERF[perf_year]=y-1)+((PERF[perf_year]=y)*(PERF[perf_month]<=m))),"N/A"),1,2,5,6), 
label,CHOOSECOLS(flt,1,2), 
data,CHOOSECOLS(flt,3,4), 
grp,GROUPBY(label,data,SUM,0,0), 
y_g,CHOOSECOLS(grp,1), 
m_g,CHOOSECOLS(grp,2), 
ym_t,CHOOSECOLS(TAA,1)&CHOOSECOLS(TAA,2), 
s_corr,XLOOKUP(y_g&m_g,ym_t,CHOOSECOLS(TAA,3)), 
s_new,CHOOSECOLS(grp,3)*(1+s_corr), 
d_corr,XLOOKUP(y_g&m_g,ym_t,CHOOSECOLS(TAA,4)), 
d_new,CHOOSECOLS(grp,4)*(1+d_corr), 
HSTACK(grp,s_new,d_new))