When a value entered in D exists in A, E and F should be populated from B and C respectively. So, when "New York" is pasted in column D, E for that row should be set to 33 and F should be set to 3. Values in A and D are unordered and column A has more rows than column D.
| A | B | C | D | E | F |
|---|---|---|---|---|---|
| Tokyo | 30 | 0 | New York | 33 | 3 |
| Istanbul | 31 | 1 | Tokyo | 30 | 0 |
| Liverpool | 32 | 2 | |||
| New York | 33 | 3 |
I'm actually using LibreOffice for this, but I could switch to Excel if this can be solved better through Excel features. Cheers!
CodePudding user response：
Try using the XLOOKUP function:
E1: =IF(ISBLANK($D1),"",XLOOKUP($D1,$A$1:$A$4,$B$1:$B$4))
F1: =IF(ISBLANK($D1),"",XLOOKUP($D1,$A$1:$A$4,$C$1:$C$4))
Copy the two formulas across to rows 2-4.