In table 1 there is a range of codes that may be repeated, but with different values for each code:
Table 1 | |
---|---|
Cod. | Value |
qwe | 2 |
asd | 3 |
zxc | 8 |
rty | 12 |
fgh | 14 |
vbn | 15 |
qwe | 16 |
zxc | 18 |
rty | 20 |
qwe | 23 |
Table 2 contains the same codes as table 1, but ordered differently:
Table 2 | |
---|---|
Cod. | Find |
asd | |
fgh | |
qwe | |
qwe | |
qwe | |
rty | |
rty | |
vbn | |
zxc | |
zxc |
I need to fetch the values from table 1 into table 2, so that:
- the first occurrence of a code in table 2 search for the first
occurrence of the same code in table 1; - the second occurrence of a code in table 2 search for the second
occurrence of the same code in table 1; - and so on.
I came up with the formula below, but to no avail:
=ÍNDICE($B$2:$B$10; CORRESP(1; ($A$2:$A$10=F2)*(CONT.SE($F$2:F2;F2)=CONT.SE($A$2:$A$10; F2)); 0))
Below is the simulation spreadsheet.
Test
You may try:
=map(J3:J12;lambda(Σ;chooserows(filter(B:B;A:A=Σ);countif(J3:Σ;Σ))))