Identifier si la clé d'une ligne est présente dans un autre tableau

La problématique

On considère que l'on a un document Excel contenant deux "feuilles" (deux onglets).

• Une feuille "data" dans laquelle on a des lignes représentant des enregistrements dont une des colonne est une clé
• Une feuille "liste" dans laquelle nous n'avons qu'une colonne qui donne une sous liste de clé

On voudrait avoir une colonne supplémentaire dans la première feuille pour dire si la ligne est présente ou pas dans la liste de la feuille "liste"1

data

	A	B	C	D	E
1	0	Nom A	Val 1	Info a
2	1	Nom B	Val 2	Info b
3	2	Nom C	Val 3	Info c
4	3	Nom D	Val 4	Info d
5	4	Nom E	Val 5	Info e

liste

	A
1	0
2	3
3	4

On voudrait que la colonne E de la feuille "data" vaille oui si la valeur de sa colonne A est présente dans la liste.

Comment faire : la solution avec EQUIV

On dit merci à ChatGPT qui m'a trouvé cette solution!

Nommer les plages de données

• Dans votre tableau d'origine (dans exemple l'onglet "data"), sélectionnez la colonne des clés et donnez lui un nom, par exemple Cles_Origine.
• Dans votre tableau de sous-liste (dans exemple l'onglet "liste"), sélectionnez la colonne des clés et donnez-lui un nom, par exemple Cles_Sous_Liste.

Ajouter une nouvelle colonne dans le tableau d'origine

• Allez dans l'onglet "data" et ajoutez une nouvelle colonne (dans l'exemple colonne E) pour indiquer la présence des clés.
• Dans la cellule de la nouvelle colonne, par exemple E2, utilisez la formule suivante :

=SI(ESTNUM(EQUIV(A1;Cles_Sous_Liste;0)); "Oui"; "Non")

• Faire une "recopie vers le bas" pour remplir toute la colonne E avec ce code.

	A	B	C	D	E
1	0	Nom A	Val 1	Info a	Oui
2	1	Nom B	Val 2	Info b	Non
3	2	Nom C	Val 3	Info c	Non
4	3	Nom D	Val 4	Info d	Oui
5	4	Nom E	Val 5	Info e	Oui

Explication

Revoyons la formule :

=SI(ESTNUM(EQUIV(A1;Cles_Sous_Liste;0)); "Oui"; "Non")

Tout est dans EQUIV().

Cette fonction prends 3 paramètres :

1. La valeur a chercher (ici la première colonne de notre ligne)
2. Le tableau dans lequel chercher cette valeur. Ici on donne son nom logique "Cles_Sous_Liste" mais on aurait tout aussi bien pu utiliser "liste!A1:A3" c'est juste moins clair et on s'arête à la ligne 3.
3. une option. (0=égalité, 1 A1 est inféreurs a toutes les valeurs du tableau et -1 A est supérieure aux valeurs)

Elle renvoie un entier qui donne la position dans le tableau ou #N/A si elle ne trouve pas la valeur.

On compare ensuite si la valeur est numérique avec ESTNUM() qui nous réponds VRAI ou FAUX (ce qui suffirait finalement) et on utilise SI() pour écrire "Oui" ou "Non".

Pour les Excel UK/US

Pour ceux qui utilisent des versions Excel anglophones (Excel international) il faut utiliser la formule en anglais.

=IF(ISNUMBER(MATCH(A1, Clés_Sous_Liste, 0)), "Oui", "Non")

Je trouves que le MATCH() est plus parlant que le EQUIV()... mais c'est perso.

La solution avec "RECHERCHEV"

Cette solution est plus classique mais elle en fait plus. Elle recherche la valeur données dans une matrice et, si elle la trouve elle retourne la valeur de la nième collone.

On reprends notre exemple mais on peut même, dans liste préciser si un élément fait partie ou "peut être" de la liste:

data

	A	B	C	D	E
1	0	Nom A	Val 1	Info a
2	1	Nom B	Val 2	Info b
3	2	Nom C	Val 3	Info c
4	3	Nom D	Val 4	Info d
5	4	Nom E	Val 5	Info e

liste

	A	B
1	0	oui
2	3	peut-être
3	4	oui

On nomme "Cles_Sous_Liste" la matrice A1:B3 de "liste"

Dans la colonne E on mets:

=RECHERCHEV(A1;Cles_Sous_Liste;2;FAUX)

On récupère le tableau:

	A	B	C	B	E
1	0	Nom A	Val 1	info a	oui
2	1	Nom B	Val 2	info b	#N/A
3	2	Nom C	Val 3	info c	#N/A
4	3	Nom D	Val 4	info d	peut-être
5	4	Nom E	Val 5	info e	oui

La fonction cherche son premier argument dans la matrice, et renvoie la valeur correspondante dans la colonne n (ici 2). Le FAUX à la fin dit que la recherche n'est pas partielle. On demande une égalité exacte.

Si la valeur recherchée n'est pas trouvée on retourne #N/A. Ce #N/A est interprété en erreur par Excel.

=SIERREUR(RECHERCHEV(A1;Cles_Sous_Liste;2;FAUX);"non")

Avec cette formule on obtiens bien :

	A	B	C	D	E
1	0	Nom A	Val 1	info a	oui
2	1	Nom B	Val 2	info b	non
3	2	Nom C	Val 3	info c	non
4	3	Nom D	Val 4	info d	peut-être
5	4	Nom E	Val 5	info e	oui