Search code examples
google-sheetsarray-formulas

Sheets ArrayFormula. Find nearest number by group

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.

Solution

  • 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.

    enter image description here

    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),))

    enter image description here