View original (French)

Excel: Fill a cell based on another

Solved
Alexisarnx Posted messages 1 Status Member -  
votre psychiatre Posted messages 1509 Registration date   Status Member Last intervention   -

Hello everyone,

I would like to find a conditional formula to automatically fill cells in my table.

> I fill my cell A1 with a media name (e.g.: Figaro, Libération...) from a dropdown list using data validation (the source is a list of media written on another sheet).

> I would like the cell B1 to automatically give the country of distribution for each media provided (e.g.: I enter "Figaro" in A1 using my dropdown list, and B1 is automatically filled with "France").

How should I proceed? Should I write a second list with the countries of distribution? But how can I link the two together? There are about fifty different media, and I can't mention them all in a single formula.

Thank you very much for your help.

Best regards,

3 answers

  1.
    danielc0 Posted messages 2192 Registration date   Status Member Last intervention   287
     

    Hello,

    You need to create a media table with the country alongside, like in this example:

    The formula in G3 is:

    =VLOOKUP(F3,C4:D7,2,0)

    Refer to the Excel help on the VLOOKUP function.

    Daniel

    2
  2. blackmefias_3350 Posted messages 710 Registration date   Status Member Last intervention   70
     

    Hello, I think that for this, you need to create a formula like this:

    =IF(A1="Figaro", "France", FALSE) (formula to be placed in cell B1) This means that IF in cell A1 the word is Figaro, then in cell B1 the word France will appear; otherwise, it returns FALSE.

    As this is a "data validation" list, in addition to the IF function, you need to use the MULTIPLE condition, which would give you the following formula:


    =IFS(TRUE, D6="", "", D6="Figaro", "France", D6="Times", "UK", D6="Washington Post", "USA", D6="Le Monde", "France")

    PS: I am using Office365. In the formula above, the first value is empty (D6=""), and that is why the result must also be empty (;"";), which also means that in my table that contains the values of the list, the first value of the table is empty.

    1
  3. votre psychiatre Posted messages 1509 Registration date   Status Member Last intervention   259
     

    Daniel's database principle is probably the best; if you don't want to, you can do this or

    =if(or(A1="figaro";a1="ouest france";...);"France";if(or(a1=...........);"United States" ...

    but it would be simpler to add the country column in your database and do a vlookup

    1