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:Σ;Σ))))
