Sheets ArrayFormula. Find nearest number by group

Question

Master Data

Group-Value pairs

1 |  1
1 |  2
1 |  3
2 |  5
2 |  8
3 | 10
3 | 12

Work Data

Group-Value pairs + desired result

1 | 4 | 3 (3≤4, max in group 1)
1 | 2 | 2 (2≤2, max in group 1)
2 | 6 | 5 (5≤6, max in group 2)
3 | 7 | no result (both 10 and 12 > than 7)

---

The task is to find the maximum possible matched number from a group, the number should be less or equal to the given number.

For Group 1, value 4:

=> filter Master Data (1,2,3) => find 3

Will have no problem with doing it once, need to do it with arrayformula.

My attempts to solve it were using modifications of the vlookup formula, with wrong outputs so far.

Samples and my working "arena":

https://docs.google.com/spreadsheets/d/11Cd2BGpGN-0h2bL0LQ_EpIDBKKT2hvTVHoxGC6i8uTE/edit?usp=sharing

Notes: no need to solve it in a single formula, because it may slow down the result.

Answer 1

I used

=ArrayFormula(VLOOKUP(D4:D8&text(E4:E8,"0000"),A4:A10&text(B4:B10,"0000"),1,true))

starting in J4

then

=ArrayFormula(if(--left(J4:J8)=D4:D8,--right(J4:J8,4),""))

starting in K4.

Needs further refinement but doesn't make any assumptions about max of previous group.

EDIT

So after further work it would look like this

=ArrayFormula(if(D4:D="",,
if(D4:D=
vlookup(D4:D&text(E4:E,"0000"),filter({A4:A&text(B4:B,"0000"),A4:A},A4:A<>""),2,true),
vlookup(D4:D&text(E4:E,"0000"),filter({A4:A&text(B4:B,"0000"),B4:B},A4:A<>""),2,true),"")))

A lot like @player0's solution in fact.

I guess you could make it a bit more general by doing something like

=text(B4,rept("0",ceiling(log10(max(B4:B)))))

assuming these are positive integers.

---

Alternative method

I think this is a better way. Find the start row of each group and how many rows r less than or equal to the required group/value pair are in that group. Then just go forward r-1 rows from the first line of the group to find the matching value:

=ArrayFormula(if(countifs(A4:A,D4:D,B4:B,"<="&E4:E)>0,
vlookup(
  vlookup(D4:D,{A4:A,row(A4:A)},2,false)+countifs(A4:A,D4:D,B4:B,"<="&E4:E)-1,{row(A4:A),B4:B},2,false),))

Assuming of course that the Master data is sorted by group and value - otherwise you would have to use sort():

=ArrayFormula(if(countifs(A4:A,D4:D,B4:B,"<="&E4:E)>0,
vlookup(
  vlookup(D4:D,{sort(A4:A,A4:A,1,B4:B,1),row(A4:A)},2,false)+countifs(A4:A,D4:D,B4:B,"<="&E4:E)-1,{row(A4:A),SORT(B4:B,A4:A,1,B4:B,1)},2,false),))

Answer 2

My solution was based on the technique of finding the maximum number by a row. The sample formula is here: https://docs.google.com/spreadsheets/d/1VY157ykKsCVDqEKDBp3oAVaG0LTXAz8wUCggCrFXMDM/edit#gid=628408999

My whole solution is here:

https://docs.google.com/spreadsheets/d/11Cd2BGpGN-0h2bL0LQ_EpIDBKKT2hvTVHoxGC6i8uTE/edit#gid=0

Step 1

Get joined numbers by groups from a Master Table.

1 | 3,2,1
2 | 8,5
3 | 12,10

Used offset to achieve this ?. And used vlookup to match this semi-result with work table.

Step 2

Used if + split to check if the resulted value was ? than my work value, and in the same formula used query to find the maximum by each row.

compose a query: used join + sequence

=IF(M3=0,,"select "&JOIN(", ",INDEX("max(Col"&SEQUENCE(M3)&")")))

result:

select max(Col1), max(Col2), max(Col3), max(Col4), max(Col5)

Found the maximum by each group:

=index(TRANSPOSE(QUERY(TRANSPOSE(data), "select ...")))

This final formula was the ? to solving the problem.

Note: the result: 0 of my formula means "no matches". This is fine for me.

Answer 3

try:

=INDEX(IFNA(IF(E4:E>=
 VLOOKUP(D4:D&TEXT(E4:E, "00000"), {A4:A&TEXT(FILTER(B4:B, B4:B<>""), "00000"), B4:B}, 2), 
 VLOOKUP(D4:D&TEXT(E4:E, "00000"), {A4:A&TEXT(FILTER(B4:B, B4:B<>""), "00000"), B4:B}, 2), 0)))