Tutoriel Excel: recherche bidirectionnelle avec INDEX et MATCH approximatif

Table des matières

Raccourcis associés

Dans cette vidéo, nous verrons comment créer une recherche bidirectionnelle avec INDEX et MATCH, en utilisant une correspondance approximative.

Ici, nous avons un simple calculateur de coût, qui recherche le coût en fonction de la largeur et de la hauteur d'un matériau. La correspondance doit être approximative. Par exemple, si la largeur est de 250 et la hauteur de 325, le résultat correct est de 1 800 $.

Si la largeur est de 450 et la hauteur reste à 325, le résultat correct est 3 600 $.

Nous pouvons créer une formule qui effectue cette recherche en utilisant INDEX et MATCH.

Tout d'abord, faisons fonctionner INDEX comme une preuve de concept, en codant en dur une partie de la formule. C'est un excellent moyen de vous assurer que vous avez la bonne idée, avant de commencer.

Ainsi, avec les données de notre tableau comme tableau, et avec une largeur de 450 et une hauteur de 325, INDEX va avoir besoin d'un numéro de ligne de 3 et d'un numéro de colonne de 4, pour récupérer la valeur correcte dans le tableau . Cela fonctionne bien mais bien sûr cela ne changera pas car les valeurs sont codées en dur.

Ensuite, configurons les fonctions MATCH dont nous avons besoin pour calculer ces valeurs.

Pour obtenir la valeur de width, qui est le numéro de ligne dans INDEX, nous utiliserons la valeur de recherche de M7 et les valeurs de la colonne B comme tableau de recherche. Pour le type de correspondance, nous voulons utiliser 1 pour une correspondance approximative, car les valeurs sont triées par ordre croissant. Le résultat est 4.

Pour obtenir la hauteur, qui est la colonne dans INDEX, nous utiliserons à nouveau MATCH avec la valeur de M8, les valeurs de hauteur de la ligne 6. Encore une fois, le type de correspondance est à nouveau défini sur 1 pour une correspondance approximative. Le résultat est 3.

Maintenant, si je change la largeur à 350 et la hauteur à 550, nous obtenons un nouvel ensemble de résultats.

Ces valeurs sont exactement ce dont nous avons besoin pour INDEX. Alors maintenant, je vais simplement copier et coller les fonctions MATCH dans la formule INDEX d'origine.

La largeur entre pour le numéro de ligne.

Et la hauteur entre pour le numéro de colonne.

Nous avons maintenant une recherche dynamique qui calcule correctement le coût en fonction de la largeur et de la hauteur, ce qui correspond approximativement.