Utilisez INDEX avec deux fonctions MATCH à correspondance exacte pour renvoyer la quantité à l’intersection d’une ligne d’élément graphique et d’une colonne de format. Ce guide présente une petite feuille de calcul d’éléments graphiques, explique la formule et montre une autre méthode avec XLOOKUP imbriquées pour les versions Mac compatibles.

Quelle méthode de recherche fonctionne dans Excel pour Mac ?

Pour cette tâche, vous disposez d’un nom d’élément graphique, comme Dot Grid, et d’un format, comme PNG. Vous voulez obtenir la quantité à leur intersection. On appelle couramment cela une recherche bidimensionnelle.

Microsoft indique que INDEX et MATCH sont disponibles dans Excel pour Microsoft 365 pour Mac, Excel 2024 pour Mac et Excel 2021 pour Mac. Les deux méthodes de cet article fonctionnent dans ce périmètre de compatibilité documenté pour Mac. Consultez la documentation Microsoft sur la fonction INDEX et la documentation sur la fonction MATCH.

XLOOKUP est également documentée pour ces éditions Mac. Microsoft précise explicitement qu’elle n’est pas disponible dans Excel 2016 et Excel 2019, même si ces éditions figurent dans la liste générale de compatibilité de la page. Si votre édition ne dispose pas de XLOOKUP, utilisez la méthode avec INDEX et MATCH. Microsoft : fonction XLOOKUP

Commencez par INDEX et MATCH si vous souhaitez examiner séparément les positions de la ligne et de la colonne pendant votre apprentissage. Choisissez les fonctions XLOOKUP imbriquées si vous utilisez déjà cette fonction et préférez sa correspondance exacte par défaut.

Configurer une petite grille de quantités d’éléments graphiques

Dans une feuille de calcul vierge, saisissez les données fictives suivantes dans A1:D5. La colonne la plus à gauche du tableau ci-dessous correspond à la colonne de feuille de calcul A ; n’ajoutez pas de colonne supplémentaire pour les numéros de ligne. Ces quantités sont données à titre d’exemple.

Élément graphique SVG PNG JPG
Tile Arc 12 18 6
Dot Grid 8 24 10
Loop Ribbon 15 9 0
Soft Square 7 14 5

Chaque élément graphique occupe une ligne et chaque format une colonne. Pour cet exemple, utilisez des libellés d’éléments graphiques et des en-têtes de format uniques. Saisissez un zéro numérique explicite lorsque la quantité est connue comme étant nulle ; vérifiez les quantités non résolues avant d’utiliser la grille.

Créez une zone de recherche distincte dans la même feuille de calcul :

Cellule À saisir
F1 Élément graphique à rechercher
F2 Dot Grid
G1 Format à rechercher
G2 PNG
H1 Quantité

Les trois plages source ont des rôles différents : A2:A5 contient les libellés des éléments graphiques, B1:D1 contient les en-têtes de format et B2:D5 contient uniquement les quantités. En plaçant les en-têtes en dehors de la plage des quantités, vous alignez correctement les positions relatives.

Renvoyer la quantité avec INDEX et deux fonctions MATCH

Sélectionnez H2, saisissez la formule complète ci-dessous, puis appuyez sur Retour :

=INDEX($B$2:$D$5,MATCH(F2,$A$2:$A$5,0),MATCH(G2,$B$1:$D$1,0))

Le résultat devrait être 24 : la quantité pour Dot Grid dans la colonne PNG. Les instructions Microsoft pour Mac indiquent de sélectionner une cellule de résultat, de saisir une formule commençant par =, puis d’appuyer sur Retour. Microsoft : saisir une formule

Comprendre les deux positions

La première fonction MATCH trouve la position de l’élément graphique dans A2:A5 ; la deuxième trouve celle du format dans B1:D1. Le dernier argument 0 demande une correspondance exacte. Sans cet argument, MATCH utilise par défaut une correspondance approximative qui exige que les données soient triées. Microsoft : fonction MATCH

Pour les valeurs fournies, les deux calculs internes sont les suivants :

=MATCH(F2,$A$2:$A$5,0)

Résultat : 2.

=MATCH(G2,$B$1:$D$1,0)

Résultat : 2.

La formule complète sélectionne donc la ligne 2 et la colonne 2 dans la plage des quantités :

=INDEX($B$2:$D$5,2,2)

Il s’agit de la cellule de feuille de calcul C3, qui contient 24. INDEX utilise les positions dans la plage fournie ; sa deuxième ligne correspond donc ici à la ligne 3 de la feuille de calcul. Microsoft indique que la fourniture des deux positions renvoie la valeur à leur intersection. Microsoft : fonction INDEX

Garder les plages source fixes lors de la copie

Les signes dollar verrouillent les plages source. Les références d’entrée F2 et G2 restent relatives. Ainsi, si vous copiez H2 vers le bas, dans H3, la formule suivante lira F3 et G3 tout en utilisant la même grille source. Sur Mac, vous pouvez également sélectionner une référence dans la barre de formule et appuyer sur Command+T pour faire défiler les types de références. Microsoft : basculer entre les références relatives et absolues

Par exemple, saisissez Tile Arc dans F3 et JPG dans G3, puis copiez H2 dans H3. Le nouveau résultat devrait être 6. Vous obtenez ainsi une petite liste de demandes sans modifier la grille d’éléments graphiques d’origine.

Utiliser XLOOKUP imbriquées dans les versions Mac compatibles

Avec la même feuille de calcul et les mêmes valeurs de recherche, vous pouvez saisir cette formule dans H2 à la place :

=XLOOKUP(F2,$A$2:$A$5,XLOOKUP(G2,$B$1:$D$1,$B$2:$D$5))

La formule renvoie 24 pour Dot Grid et PNG. La documentation Microsoft comprend un exemple avec des fonctions XLOOKUP imbriquées qui effectuent une recherche verticale et horizontale pour renvoyer une valeur à l’intersection. La correspondance exacte est utilisée par défaut. Microsoft : fonction XLOOKUP

Dans cet exemple, la recherche interne sélectionne la colonne de quantités PNG : 18, 24, 9 et 14. La recherche externe sélectionne l’entrée alignée sur Dot Grid. En utilisant les mêmes trois plages source, vous pouvez facilement comparer cette méthode à la formule précédente.

Vérifier le résultat avant d’utiliser une grille plus grande

Utilisez les cas suivants comme petit test d’acceptation. Modifiez F2 et G2 pour chaque ligne, puis comparez H2 au résultat attendu. Rétablissez ensuite Dot Grid et PNG si vous souhaitez conserver l’exemple d’origine.

Valeur de l’élément graphique Valeur du format Résultat attendu Vérification
Dot Grid PNG 24 Intersection centrale
Tile Arc JPG 6 Premier élément graphique et dernier format
Soft Square SVG 7 Dernier élément graphique et premier format
Loop Ribbon JPG 0 Quantité explicitement nulle
Tile Arcs PNG #N/A Libellé d’élément graphique absent
Dot Grid TIFF #N/A En-tête de format absent

Si un nombre est incorrect, vérifiez les limites des plages avant de changer de méthode de recherche. Pour cette grille, A2:A5 doit correspondre aux quatre lignes de B2:D5, et B1:D1 doit correspondre à ses trois colonnes. Par exemple, inclure A1 uniquement dans la plage de recherche des éléments graphiques décalerait la position calculée par rapport à la ligne de quantité prévue.

Lorsque vous ajoutez un élément graphique à cette grille, en ligne 6, mettez à jour la plage des éléments graphiques en A2:A6 et la plage des quantités en B2:D6. Lorsque vous ajoutez un format en colonne E, mettez à jour la plage des en-têtes en B1:E1 et la plage des quantités en B2:E5. Ces modifications par paire préservent la disposition utilisée par les formules.

Les libellés en double demandent également de l’attention. Une fonction MATCH à correspondance exacte renvoie la première position correspondante et ne distingue pas les majuscules des minuscules ; elle interprète aussi * et ? comme des caractères génériques dans les recherches textuelles. Pour cette configuration, utilisez des libellés distincts sans ces caractères. Microsoft : fonction MATCH

Afficher un message utile si un libellé est introuvable

Une fois la recherche de base opérationnelle, vous pouvez remplacer l’erreur de correspondance introuvable par un message explicite :

=IFNA(INDEX($B$2:$D$5,MATCH(F2,$A$2:$A$5,0),MATCH(G2,$B$1:$D$1,0)),"Check asset or format")

IFNA renvoie votre message lorsque la formule incluse produit #N/A ; sinon, elle conserve le résultat. Cette fonction est documentée pour Microsoft 365 pour Mac, Excel 2024 pour Mac et Excel 2021 pour Mac. Microsoft : fonction IFNA

Dans cet exemple, Tile Arcs produit Vérifier l’élément graphique ou le format, tandis que Loop Ribbon avec JPG renvoie toujours 0. Cette distinction permet de différencier un libellé introuvable d’une quantité enregistrée à zéro. Si une quantité source contient elle-même #N/A, cette fonction de remplacement changera aussi cette erreur ; vérifiez donc la cellule source lorsque les libellés semblent corrects.

Pour un usage quotidien, veillez à ce que les noms des éléments graphiques soient uniques, préservez l’alignement des trois plages source et vérifiez quelques intersections connues après avoir modifié la grille. Vous pourrez ensuite obtenir la quantité de chaque élément graphique demandé en changeant les valeurs de l’élément graphique et du format.

Sources