Try to achieve: Vlookup (Table 2 > Table 1 ) to Match the Nth Occurrence in Google Sheets.
Table 1
| TABLE 1 | |
|---|---|
| Data | value |
| a | 10 |
| a | 20 |
| a | 30 |
| a | 40 |
| b | 50 |
| c | 60 |
| c | 70 |
Table 2
| TABLE 2 | |
|---|---|
| Data | helper: count data occurrences value |
| a | 1 |
| a | 2 |
| a | 3 |
| a | 4 |
| b | 1 |
| c | 1 |
| c | 2 |
Issue: The FILTER and INDEX combo works fine but (my understanding) can't be converted in an array-formula.
INDEX(FILTER($F$3:$G,$F$3:$F=A3),B3,2)
INDEX(FILTER($F$3:$G,$F$3:$F=A4),B4,2)
INDEX(FILTER($F$3:$G,$F$3:$F=A5),B5,2)
INDEX(FILTER($F$3:$G,$F$3:$F=A6),B6,2)
INDEX(FILTER($F$3:$G,$F$3:$F=A7),B7,2)
INDEX(FILTER($F$3:$G,$F$3:$F=A8),B8,2)
INDEX(FILTER($F$3:$G,$F$3:$F=A9),B9,2)
| TABLE 2 | |||
|---|---|---|---|
| Data | data occurrences | INDEX FILTER | FORMULA RESULT |
| a | 1 | INDEX(FILTER($F$3:$G,$F$3:$F=A3),B3,2) | 10 |
| a | 2 | INDEX(FILTER($F$3:$G,$F$3:$F=A4),B4,2) | 20 |
| a | 3 | INDEX(FILTER($F$3:$G,$F$3:$F=A5),B5,2) | 30 |
| a | 4 | INDEX(FILTER($F$3:$G,$F$3:$F=A6),B6,2) | 40 |
| b | 1 | INDEX(FILTER($F$3:$G,$F$3:$F=A7),B7,2) | 50 |
| c | 1 | INDEX(FILTER($F$3:$G,$F$3:$F=A8),B8,2) | 60 |
| c | 2 | INDEX(FILTER($F$3:$G,$F$3:$F=A9),B9,2) | 70 |
My attempt: Instead of using INDEX, I am trying to use VLOOKUP (which works well inside Arrayformulas)
ARRAYFORMULA(IF(G3:G9="",(VLOOKUP(F3:G,FILTER(A3:A=F3:F),2,B3:B))))
| Data | data occurrences | INDEX FILTER | FORMULA RESULT |
|---|---|---|---|
| a | 1 | ARRAYFORMULA(IF(G3:G9="",(VLOOKUP(F3:G,FILTER(A3:A=F3:F),2,B3:B)))) | FALSE |
| a | 2 | FALSE | |
| a | 3 | FALSE | |
| a | 4 | FALSE | |
| b | 1 | FALSE | |
| c | 1 | FALSE | |
| c | 2 | FALSE | |
| c | 2 | FALSE |
Error: I get all rows "FALSE"
Any help?
CodePudding user response：
MAP() function may work. Try-
=MAP(A3:A9,B3:B9,LAMBDA(x,y,INDEX(FILTER(G3:G,F3:F=x),y)))
To make it more dynamic for input columns try-
=MAP(A3:INDEX(A3:A,COUNTA(A3:A)),B3:INDEX(B3:B,COUNTA(B3:B)),LAMBDA(x,y,INDEX(FILTER(G3:G,F3:F=x),y)))
CodePudding user response：
you can try this (helper column skipped)
=BYROW(BYROW(D2:D,LAMBDA(aixx,IF(aixx="",,aixx&COUNTIF(D2:aixx,aixx)))),LAMBDA(z,xlookup(z,BYROW(A2:INDEX(A:A,ROW(LOOKUP("zzz",A:A))),LAMBDA(aixx,aixx&COUNTIF(A2:aixx,aixx))),B2:INDEX(B:B,ROW(LOOKUP(2^99,B:B))),)))
