AND function for each column in a spilled range

[SStandrin]

So currently I have a range of numbers B1:O6 (this range will eventually get larger), to which I am comparing to A1:A6 (this too will get larger). i.e. Each cell in row 1 is compared to A1, Each cell in row 2 is compared to A2, etc.

To make this comparison I have a Spill Cell (B8) with the formula =IF(A1:A6<B1:O6,"TRUE","FALSE") that outputs a series of TRUE and FALSE as to whether a cell in the range is larger than the cell in the A column with the same row number.

Currently I'm using =AND(EXACT(B8:B13,"TRUE")) and then dragging this along horizontally to show whether the referenced TRUE/FALSE column is all TRUE or not, this outputs the results I wish to get, but I wish to have just a single cell with a spill formula that does the same job. Is there a way to do this?

My end goal is to just have one cell with some sort of russian nesting doll type of thing going on where it just spits out a single TRUE/FALSE as to whether or not there is a column in the range that is greater than A1:A6 for every single row. So I was planning on taking whatever AND spill function I could figure out and slap an OR function onto it. But I can't even figure out the AND part.

[#2 · Accepted solution]

@SStandrin I think this is what you were asking for... hope it helps.

I first did the formulas one at a time , then in the bottom I did all of them inside one formula with let.

To make it dynamic I had to transform the data into tables.

[#3]

@SStandrin If you have the lambda function, I believe you could also try:

=LET(isColGreater, BYCOL(B1:O6, LAMBDA(col, SUM(--(col<=A1:A6))=0)),

isColGreater)

[#4]

that is great! I still don´t have lambda in excel... looks really good and simple formula

[#5]

@alannavarro Thank you very much, this is exactly what I needed.

[#6]

@JMB17 I've not dabbled with the Lambda stuff, although by the sounds of it I should probably have a look at it sooner rather than later.