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":

CodePudding user response：

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