excelexcel-formulalibreofficedata-entry

Set column E from column B when a value from D exists in A

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!

Solution

  • 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.