Combiner plusieurs fonctions en une seule
Bonjour,
En entrant un code postal dans une cellule, j'ai besoin que le nom de l'arrondissement s'affiche automatiquement dans une autre cellule.
Voici donc les formules en questions :
=IF(AND(B2>="H1A";B2<"H1B");"Pointe-aux-Trembles / RDP";
IF(AND(B2>="H1B";B2<"H1C");"Montréal-Est / Pointes-aux-Trembles";
IF(AND(B2>="H1C";B2<"H1E");"Rivière-des-Prairies";
IF(AND(B2>="H1E";B2<"H1G");"Rivière-des-Prairies";
IF(AND(B2>="H1G";B2<"H1H");"Montréal-Nord";
IF(AND(B2>="H1H";B2<"H1J");"Montréal-Nord";
IF(AND(B2>="H1J";B2<"H1K");"Anjou";
IF(AND(B2>="H1K";B2<"H1L");"Anjou / Mercier";
IF(AND(B2>="H1L";B2<"H1M");"Mercier / Hochelaga-Maisonneuve";
IF(AND(B2>="H1M";B2<"H1N");"Mercier";
IF(AND(B2>="H1N";B2<"H1P");"Mercier";
IF(AND(B2>="H1P";B2<"H1R");"Saint-Léonard";
IF(AND(B2>="H1R";B2<"H1S");"Saint-Léonard";
IF(AND(B2>="H1S";B2<"H1T");"Saint-Léonard";
IF(AND(B2>="H1T";B2<"H1V");"Rosemont / Saint-Léonard";
IF(AND(B2>="H1V";B2<"H1W");"Mercier / Hochelaga-Maisonneuve";
IF(AND(B2>="H1W";B2<"H1X");"Mercier / Hochelaga-Maisonneuve";
IF(AND(B2>="H1X";B2<"H1Y");"Rosemont / La Petite-Patrie";
IF(AND(B2>="H1Y";B2<"H1Z");"Rosemont / La Petite-Patrie";
IF(AND(B2>="H1Z";B2<"H2A");"Saint-Michel / Villeray";
IF(AND(B2>="H2A";B2<"H2B");"Saint-Michel / Villeray";
IF(AND(B2>="H2B";B2<"H2C");"Ahuntsic";
IF(AND(B2>="H2C";B2<"H2E");"Ahuntsic";
IF(AND(B2>="H2E";B2<"H2G");"Villeray";
IF(AND(B2>="H2G";B2<"H2H");"Rosemont / La Petite-Patrie";
IF(AND(B2>="H2H";B2<"H2J");"Plateau-Mont-Royal";
IF(AND(B2>="H2J";B2<"H2K");"Plateau-Mont-Royal";
IF(AND(B2>="H2K";B2<"H2L");"Centre-Sud";
IF(AND(B2>="H2L";B2<"H2M");"Centre-Sud / Plateau";
IF(AND(B2>="H2M";B2<"H2N");"Ahuntsic";
IF(AND(B2>="H2N";B2<"H2P");"Ahuntsic";
IF(AND(B2>="H2P";B2<"H2R");"Villeray";
IF(AND(B2>="H2R";B2<"H2S");"Villeray";
IF(AND(B2>="H2S";B2<"H2T");"Rosemont / La Petite-Patrie";
IF(AND(B2>="H2T";B2<"H2V");"Plateau-Mont-Royal";
IF(AND(B2>="H2V";B2<"H2W");"Outremont / Plateau";
IF(AND(B2>="H2W";B2<"H2X");"Plateau-Mont-Royal";
IF(AND(B2>="H2X";B2<"H2Y");"Ville-Marie";
IF(AND(B2>="H2Y";B2<"H2Z");"Ville-Marie";
IF(AND(B2>="H2Z";B2<"H3A");"Ville-Marie";
IF(AND(B2>="H3A";B2<"H3B");"Centre-Ville";
IF(AND(B2>="H3B";B2<"H3C");"Centre-Ville";
IF(AND(B2>="H3C";B2<"H3E");"Griffintown / Ville-Marie";
IF(AND(B2>="H3E";B2<"H3G");"L’Île-Des-Soeurs / Verdun";
IF(AND(B2>="H3G";B2<"H3H");"Centre-Ville";
IF(AND(B2>="H3H";B2<"H3J");"Centre-Ville";
IF(AND(B2>="H3J";B2<"H3K");"Sud-Ouest";
IF(AND(B2>="H3K";B2<"H3L");"Pointe-Saint-Charles";
IF(AND(B2>="H3L";B2<"H3M");"Ahuntsic";
IF(AND(B2>="H3M";B2<"H3N");"Cartierville";
IF(AND(B2>="H3N";B2<"H3P");"Parc-Extension";
IF(AND(B2>="H3P";B2<"H3R");"Mont-Royal / NDG / CDN";
IF(AND(B2>="H3R";B2<"H3S");"Mont-Royal / NDG / CDN";
IF(AND(B2>="H3S";B2<"H3T");"Côte-des-Neiges";
IF(AND(B2>="H3T";B2<"H3V");"Côte-des-Neiges";
IF(AND(B2>="H3V";B2<"H3W");"Côte-des-Neiges";
IF(AND(B2>="H3W";B2<"H3X");"Côte-des-Neiges";
IF(AND(B2>="H3X";B2<"H3Y");"Hampstead / NDG / CDN";
IF(AND(B2>="H3Y";B2<"H3Z");"Westmount / Plateau";
IF(AND(B2>="H3Z";B2<"H4A");"Westmount / Plateau";
IF(AND(B2>="H4A";B2<"H4B");"Notre-Dame-de-Grâce";
IF(AND(B2>="H4B";B2<"H4C");"Notre-Dame-de-Grâce";
IF(AND(B2>="H4C";B2<"H4E");"Saint-Henri / Sud-Ouest";
IF(AND(B2>="H4E";B2<"H4G");"Ville Émard / Sud-Ouest";
IF(AND(B2>="H4G";B2<"H4H");"Verdun";
IF(AND(B2>="H4H";B2<"H4J");"Verdun";
IF(AND(B2>="H4J";B2<"H4K");"Cartierville";
IF(AND(B2>="H4K";B2<"H4L");"Cartierville";
IF(AND(B2>="H4L";B2<"H4M");"Saint-Laurent";
IF(AND(B2>="H4M";B2<"H4N");"Saint-Laurent";
IF(AND(B2>="H4N";B2<"H4P");"Saint-Laurent / Mont-Royal";
IF(AND(B2>="H4P";B2<"H4R");"Mont-Royal / NDG / CDN";
IF(AND(B2>="H4R";B2<"H4S");"Saint-Laurent";
IF(AND(B2>="H4S";B2<"H4T");"Saint-Laurent";
IF(AND(B2>="H4T";B2<"H4V");"Saint-Laurent";
IF(AND(B2>="H4V";B2<"H4W");"Côte-Saint-Luc";
IF(AND(B2>="H4W";B2<"H4X");"Côte-Saint-Luc / NDG / CDN / Saint-Laurent";
IF(AND(B2>="H4X";B2<"H4Y");"Montréal-Ouest";
IF(AND(B2>="H4Y";B2<"H8N");"Dorval";
IF(AND(B2>="H8N";B2<"H8P");"LaSalle";
IF(AND(B2>="H8P";B2<"H8R");"LaSalle";
IF(AND(B2>="H8R";B2<"H8S");"Ville Saint-Pierre / LaSalle / Lachine";
IF(AND(B2>="H8S";B2<"H8T");"Lachine";
IF(AND(B2>="H8T";B2<"H8Y");"Lachine";
IF(AND(B2>="H8Y";B2<"H8Z");"Roxboro / Pierrefonds";
IF(AND(B2>="H8Z";B2<"H9A");"Pierrefonds";
IF(AND(B2>="H9A";B2<"H9B");"Dollard-des-Ormeaux / Pierrefonds";
IF(AND(B2>="H9B";B2<"H9C");"Dollard-des-Ormeaux";
IF(AND(B2>="H9C";B2<"H9E");"L’Île-Bizard";
IF(AND(B2>="H9E";B2<"H9G");"L’Île-Bizard";
IF(AND(B2>="H9G";B2<"H9H");"Dollard-des-Ormeaux";
IF(AND(B2>="H9H";B2<"H9J");"Sainte-Geneviève / Pierrefonds";
IF(AND(B2>="H9J";B2<"H9K");"Kirkland / Pierrefonds";
IF(AND(B2>="H9K";B2<"H9P");"Senneville / Pierrefonds";
IF(AND(B2>="H9P";B2<"H9R");"Dorval";
IF(AND(B2>="H9R";B2<"H9S");"Pointe-Claire";
IF(AND(B2>="H9S";B2<"H9W");"Dorval";
IF(AND(B2>="H9W";B2<"H9X");"Beaconsfield";
IF(AND(B2>="H9X";B2<"H9Z");"Sainte-Anne-de-Bellevue";
Comment faire pour combiner toutes ces fonctions en une seule, svp?
Un gros merci à l'avance pour votre aide!
Bonsoir @ tous,
Un fichier exemple vaut mieux qu'un long discours
@ + +
Bonsoir,
une manière de simplifier la formule est d'utiliser l'instruction recherchev (vlookup en anglais) pour rechercher l'arrondissement dans une table sur base de son code, mais cela suppose de préparer les données
voir exemple
le tableau des données contenant le code suivi du libellé de l'arrondissement
en sheet2, un exemple d'utilisation de recherchev(vlookup)
Merci à tous! C'est très apprécié!