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.

 

[willbaggs]

Okay, post#6 is a piggy backing off of someone else's question... in hindsight, i should have posted by own question initially...

Also, I mentioned above that the results are not correct!! I will expand more in another reply.

 

[willbaggs]

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.

Hi - I've tried to amend this to get the correct results but I'm not able to (the 3 smallest aren't showing in the results). Do you have any other ideas or ways of getting the desired results in my example?

For clarity, the results are not correct... I would expect the following results from the Smallest 3 example of results:

slug, dog, rat
rat, cat, dog
slug, rat, dog
cat, slug, rat
slug, cat, dog
cat, dog, slug

Not the ones above.

Thanks in advance.

 

[Fluff]

How about

Fluff.xlsm
	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		slug	dog	rat		cat	rat	dog
3		48	24	50	99		rat	cat	dog		slug	dog	cat
4		37	15	20	2		slug	rat	dog		cat	dog	rat
5		25	40	45	33		cat	slug	rat		dog	rat	slug
6		54	65	60	20		slug	cat	dog		rat	dog	cat
7		30	35	30	30		cat	dog	slug		rat	cat	dog
Data

Cell Formulas
Range	Formula
G2:I7	G2	=TEXTSPLIT(TEXTJOIN(",",,BYROW(B2:E7,LAMBDA(r,TEXTJOIN("|",,TAKE(SORTBY(B1:E1,r,1),,3))))),"|",",")
K2:M7	K2	=TEXTSPLIT(TEXTJOIN(",",,BYROW(B2:E7,LAMBDA(r,TEXTJOIN("|",,TAKE(SORTBY(B1:E1,r,-1),,3))))),"|",",")
Dynamic array formulas.

 

[willbaggs]

Works great, thanks fluff.

---------------------------------------
Solution to willbaggs' additional question in the comments of this original question - which is not quite the same as the original question.

 

[Fluff]

Glad we could help & thanks for the feedback.