spill data over columns and rows with Byrow/lambda

Hi,
I have this file with 2 tabs, they both have the same data,
but I need to make the comparison to ensure they do match.

so in 'HAN' tab I'm xlookuping by account to tab '21542'
by using this formula (I509)
=XLOOKUP(TEXT(F509,0),TEXT('21542'!$A$507#,0),'21542'!$B$507#)

This would give me the result for both columns, but I need to drag it down to next rows.

In order to make it dynamic I was trying to use a BYROW/LAMBDA function, but it results in giving just one column out of the two.
=BYROW(TEXT(F509#,0),LAMBDA(r,XLOOKUP(r,TEXT('21542'!A507#,0),'21542'!B507#)))

Can anyone help understand what is the correct syntax to achieve the results like in I509 - J526 and have it spill ?

Thanks.

Administrative Note:

FUNCTION NAMES ONLY - GENERIC TITLE

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

Thanks.

Where have you mocked up what you want? I can see only two formulae producing the same results - I need also to see a manually created expected set of results.

Forget that - I see what you mean!

XLOOKUP returns a single array. What's wrong with the HSTACK option?

Is this what you wanted:

=DROP(REDUCE(0,F509#,LAMBDA(x,y,VSTACK(x,XLOOKUP(TEXT(y,0),TEXT('21542'!$A$507#,0),'21542'!$B$507#)))),1)

but I'm unsure what was wrong with the original....

Originally Posted by Glenn Kennedy

I'm unsure what was wrong with the original....

BYROW function returns always only 1 cell for each row

Originally Posted by HansDouwe

BYROW function returns always only 1 cell for each row

I meant what was wrong with the HSTACK-SUMIF....

I asked the same in post #4 - no response.

OK, not problem. At least you got sorted out...

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.