Compare individually the values from two different size ranges

Mds1

New Member
Joined
Mar 30, 2024
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Hello!
Let’s say range1is a spilled range that contains 3 values. We have another range called range2 that is fixed and contains 5 elements.
I want to compare first value=20 from range1 with all the values from range2 and get first value that is greater or equal to 20. After this, go to the second value from range1 and compare and get a result. And so on.
How can I achieve this dynamically?
(Let’s say range1 can have more or less elements). Thank you in advance for the help!

Example:
Range1 Range2
20 15
30 20
40 30
45
60
Desired outcome:
20
30
45
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Shouldn't the last outcome be 30 since that's the first value greater or equal to 30? Why is it 45?
Secondly, you said you wanted "greater or equal to" since 20 is equal or greater than 20. The second result should be 20. Why 30?
 
Upvote 0
I tried to write in text, my bad.
I wanted to say:
Range1 = {20;30;40}
Range2 = {15;20;30;45;60}
To reduce the ambiguity,
Let it be:
Range1 = {1,2,3}
Range2 = {4,5,6}
In this case, for 1, 2 and 3 the result will be {4;4;4} because 4 in the first number greater than all of them.
 
Upvote 0
How about this?

Book1
ABCDE
1Range1Range2Answer
2144
325
436
5
Sheet14
Cell Formulas
RangeFormula
A2:A4A2=SEQUENCE(3)
D2D2=XLOOKUP(TRUE,MAX(A2#)<B2:B4,B2:B4,,0,1)
Dynamic array formulas.
 
Upvote 0
I thought about it like this:
MSEPT_v1.1.xlsx
ABCDE
1AB
22015
33030
44045
560
675
7Result:
8i=1130
9i=2230
10i=3345
11
12
Sheet3
Cell Formulas
RangeFormula
A2:A4A2=SEQUENCE(3,1,20,10)
B2:B6B2=SEQUENCE(5,1,15,15)
C8:C10C8=CHOOSEROWS(FILTER($B$2#,$B$2#>=IF(ISBLANK($B$2#),,CHOOSEROWS($A$2#,B8)),),1)
Dynamic array formulas.


The idea of this thread was if I can achieve a "spill" result, so that can be dinamically changed.
Thank you for your response! :)
But I changed my mind and I am using tables right now. Maybe this can be achieved somehow :).
 
Upvote 0
Book1
ABC
1AB
2201530
3303030
4404545
560
675
Sheet15
Cell Formulas
RangeFormula
A2:A4A2=SEQUENCE(3,1,20,10)
B2:B6B2=SEQUENCE(5,1,15,15)
C2:C4C2=BYROW(A2#,LAMBDA(r,XLOOKUP(TRUE,r<=B2#,B2#)))
Dynamic array formulas.
 
Upvote 1
Solution
This is so good. Everyday you learn something. Thank you very much! I'm new to lambda functions but I will get better.
I wish you all the best! Have a great day! :)
 
Upvote 0

Forum statistics

Threads
1,223,871
Messages
6,175,092
Members
452,612
Latest member
MESTeacher

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top