XLOOKUP within LAMBDA BYCOL

[samdthompson]

Hello, I am wanting to spill an xlookup of a maximum number but am hitting a #VALUE error. The basic format is:

week	cat	rat	intended result	basic intent
2	34	16	cat	=xlookup(max(b2:c7),$b$2:$c$7,$b$1:$c$1))
3	48	24	cat
4	37	15	cat
5	25	40	rat
6	54	45	cat
7	38	39	rat

I get that xlookup wont work in this instance so would like to use BYROW but I cannot seem to get it working. I figured something like:

=BYROW(b2:c7,LAMBDA(v,a,r,XLOOKUP(v,a,r)(max(b2:c7),b2:c7,b1:c1)) would work but no dice. Since there is a substantial variation in the number of possible rows, I do want a spilling result.

And yes I get that I should be using the downloadable mini sheet thing but that would require me to have admin rights to install on my laptop.

 

[Eric W]

If I understand correctly, this is all you need:

Book1
	A	B	C	D
1	Week	Cat	Rat	Intended Result
2	2	34	16	Cat
3	3	48	24	Cat
4	4	37	15	Cat
5	5	25	40	Rat
6	6	54	45	Cat
7	7	38	39	Rat
8
Sheet8

Cell Formulas
Range	Formula
D2:D7	D2	=IF(B2:B7>C2:C7,B1,C1)
Dynamic array formulas.

 

[samdthompson]

If I understand correctly, this is all you need:

Book1
	A	B	C	D
1	Week	Cat	Rat	Intended Result
2	2	34	16	Cat
3	3	48	24	Cat
4	4	37	15	Cat
5	5	25	40	Rat
6	6	54	45	Cat
7	7	38	39	Rat
8
Sheet8

Cell Formulas
Range	Formula
D2:D7	D2	=IF(B2:B7>C2:C7,B1,C1)
Dynamic array formulas.

your right, but i have a bunch more columns to deal with, I needed to truncate for simplicity. There should be something like 26 columns.

 

[StephenCrump]

Like this perhaps:

	A	B	C	D	E	F	G
1		cat	rat	dog	slug		Result
2		34	16	2	1		cat
3		48	24	50	99		slug
4		37	15	20	2		cat
5		25	40	45	33		dog
6		54	65	60	20		rat
7		40	39	30	40		cat
ColorCount

Cell Formulas
Range	Formula
G2:G7	G2	=LET(d,B2:E7,hdr,B1:E1,INDEX(hdr,BYROW(d=BYROW(d,LAMBDA(r,MAX(r))),LAMBDA(r,MATCH(TRUE,r,)))))
Dynamic array formulas.

Note that this will display only one result per row, even if there are duplicates.

 

[samdthompson]

This is exactly right. Thanks so very much.

Like this perhaps:

	A	B	C	D	E	F	G
1		cat	rat	dog	slug		Result
2		34	16	2	1		cat
3		48	24	50	99		slug
4		37	15	20	2		cat
5		25	40	45	33		dog
6		54	65	60	20		rat
7		40	39	30	40		cat
ColorCount

Cell Formulas
Range	Formula
G2:G7	G2	=LET(d,B2:E7,hdr,B1:E1,INDEX(hdr,BYROW(d=BYROW(d,LAMBDA(r,MAX(r))),LAMBDA(r,MATCH(TRUE,r,)))))
Dynamic array formulas.

Note that this will display only one result per row, even if there are duplicates.

 

[willbaggs]

Any ideas on how to do it for spilled results across and down... Eg I'm using SMALL function to find the 3 smallest values for each person:

Using your answer above only seems to be suitable for one column spill:

 

[Cubist]

One way.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		cat	rat	dog	slug		Smallest 3				Largest 3
2		34	16	2	1		rat	dog	slug		cat	rat	dog
3		48	24	50	99		cat	rat	dog		cat	dog	slug
4		37	15	20	2		rat	dog	slug		cat	rat	dog
5		25	40	45	33		cat	rat	slug		rat	dog	slug
6		54	65	60	20		cat	dog	slug		cat	rat	dog
7		30	35	30	30		cat	dog	slug		rat	dog	slug
Sheet3

Cell Formulas
Range	Formula
G2:I7	G2	=TEXTSPLIT(TEXTJOIN(",",,BYROW(B2:E7,LAMBDA(r,TEXTJOIN("|",,TAKE(SORT(FILTER(B1:E1,r<=SMALL(r,3))),,-3))))),"|",",")
K2:M7	K2	=TEXTSPLIT(TEXTJOIN(",",,BYROW(B2:E7,LAMBDA(r,TEXTJOIN("|",,TAKE(SORT(FILTER(B1:E1,r>=LARGE(r,3))),,-3))))),"|",",")
Dynamic array formulas.

 

[willbaggs]

Thanks for responding - I'll have a play around with this... the results aren't quite correct.

 

[willbaggs]

@Fluff with all due respect, I don't believe this is the same, I did not post the initial question on this thread... can you please re-instate the question that I just posted as it is most definitely not a duplicate post, as this post is not mine. I believe I will get the answer I'm looking for with my own question being posted.

 

[Fluff]

It is a duplicate of post#6 in this thread & the other thread will remain locked.
Also you have had an answer here, but have given no information of what is wrong.