XL 2016 Afficher les bornes min et max dans lequel se trouve une valeur

[stuka]

Bonjour mes amis;

Comme à chaque fois, je me retourne vers vous dans l'espoir de trouver une réponse à mes problèmes avec Excel.
Cette fois-ci, je travaille sur le calcul qui nécessite un traitement compliqué pour moi.
Le paramètre que je veux calculer repose sur la connaissance des paramètres: HCO3, Cl, Na, SAR et EC. Comme vous voyez sur le tableau joint, les valeurs de ces derniers sont classés en groupes selon leurs valeurs. Pour chaque intervalle, il y a un autre intervalle de Qi correspondant.
Exemples:

• Si Na = 4,2 meq/l, Donc: min Na = 3 meq/l et max Na = 6 meq/l , Qmin Na = 60 et Qmax Na = 85
• Si EC = 580 µS/cm , Donc: min EC = 200 µs/cm et max EC = 750 µs/cm , Qmin EC = 85 et Qmax EC = 100 µs/cm
• Si EC = 110 µS/cm , Donc: min EC = 0 µs/cm et max EC = 200 µs/cm , Qmin EC = 0 et Qmax EC = 35 µs/cm

Ce que je veux réaliser est d'afficher les bornes min et max de chaque paramètre ainsi que celles de Qi pour les valeurs que j'ai déjà calculées dans une feuille Excel.
Je vous joins un fichier Excel dans lequel j'ai mis trois feuilles:

1. "Data" qui contient les résultats de mes analyses;
2. "Limits" qui donne les intervalles et les bornes min et max des paramètres calculés ainsi que leurs Qi respectifs (selon chaque intervalle);
3. "Qi calculation" qui contient le tableau que je veux remplir.

Mes efforts sont restés vains. Je travaille dessus depuis hier mais sans aucun résultat. J'ai combiné les fonctions: INDEX et EQUIV comme suit (exemple QminEC):
=INDEX(Limits!$K$2:$K$7; EQUIV(B2; Limits!$K$2:$L$7; 1))
mais je ne reçois que le code d'erreur N/A. Je sais donc que j'ai commis une erreur et je trouve complètement bloqué
Est-ce la bonne combinaison?
Existe-t-il une solution avec la fonction RECHERCHEV?
Je suis complètement abattu et je ne trouve plus de solution. Je vous prie donc de m'aider à résoudre ce problème.
J'attends vos réponses avec impatience car le travail est très urgent et je dois le rendre vendredi prochain.

Merci par avance et bonne soirée à vous tous.

 

[sylvanu]

Bonsoir Stuka,
Un essai en PJ pour la partie gauche en orange.
Pour la partie droite, sorry, je n'ai pas compris.

 

[Dranreb]

Bonsoir.
Si j'ai bien compris, en Q2, à propager sur 7 lignes :

=INDEX(Limits!$I$3:$I$7;EQUIV($B2;Limits!$I$3:$I$7;1))

En R2, à propager sur 7 lignes :

=INDEX(Limits!$J$3:$J$7;EQUIV($B2;Limits!$I$3:$I$7;1))

 

[stuka]

Bonsoir les amis!

Je ne sais pas comment vous décrire ma joie et ma gratitude envers vous pour votre précieuse aide pour le résultat obtenu en appliquant vos consignes. Je suis de retour de mon travail, complètement exténué mais là en accédant au forum, j'oublie ma fatigue et je retrouve mon énergie pour vous remercier et compléter mon travail.

Il reste un élément à améliorer dans mon travail qui consiste à afficher les valeurs max lorsque une valeur mesurée dépasse la valeur max indiquée dans le tableau. Donc, celle-ci devient la valeur maximale de mes mesures et non pas la valeur max affichée dans le tableau de la feuille Limits.
Je vous joins le fichier avec un exemple et une explication dans la feuille "qi calculation".

J'attends vos réponses avec impatience et vous renouvelle tous mes vifs remerciements.

respectueuses salutations.

 

[Dranreb]

Bonsoir.
Bon ben comme vu dans l'encadré, en T2, à propager sur 7 lignes :

=SI($R2<Limits!$I$7;INDEX(Limits!$J$3:$J$7;EQUIV($B2;Limits!$I$3:$I$7;1));$B$11)

 

[stuka]

Bonsoir;

Merci beaucoup Dranreb. Je ne sais pas comment vous remercier. J'ai actualisé mon fichier en utilisant votre code et je vais l'appliquer sur les autres paramètres tout en faisant très attention aux cases de référence pour les valeurs limites bien sûr!
Juste une petite remarque. J'ai peut être mal expliqué mon travail. il faut vérifier la valeur du paramètre avec la valeur max du tableau pour voir si je dois prendre la valeur max mesurée ou la valeur indiquée dans le tableau. Alors la syntaxe la plus adéquate (pour l'exemple de EC) devient:
=SI($B2<Limits!$I$7;INDEX(Limits!$J$3:$J$7;EQUIV($B2;Limits!$I$3:$I$7;1));$B$11)

Je vous joins le document final que j'ai réalisé pour voir le résultat obtenu. Enfin, j'ai pu réaliser la partie qui me tracassait pour réaliser mon travail. Je dois maintenant avancer dans mon travail pour l'exporter sur un système d'information géographique demain et le rendre dans les délais.

Sans votre aide et votre assistance, je sais pas ce que j'aurai devenu! Merci du fond du coeur pour vous tous. Longue vie et bonne continuation!