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.