Excel: Fill a cell based on another
Solvedvotre psychiatre Posted messages 1509 Registration date Status Membre 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 réponses
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
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.
