La formule de search fonctionne bien sauf pour une colonne

Eh bien, ce n'est pas vraiment l'ordre qui cause l'erreur, c'est parce que vous n'avez aucun résultat! La formule que vous utilisez tente de find -159- qu'elle ne trouve pas du tout dans la feuille d'âge. Vous avez vraiment besoin de quelque chose pour regarder dans les gammes, de sorte que si vous avez 159, il returnnera un résultat positif lorsque vous essayez de faire correspondre le 139-160 .

J'ai élaboré une formule en la construisant à partir de plus petits, mais lorsqu'elles sont assemblées, les unités répétitives rendent intimidante … De plus, c'est une formule de tableau, donc vous devez utiliser Ctrl + Shift + Entrée pour qu'elle fonctionne comme prévu. Vous pouvez encore faire glisser la formule vers le bas.

 =INDEX( INDIRECT("'"&LOOKUP($B$5,TblA)&"'!A6:A36"), IFERROR( MATCH( C7, INDEX( INDIRECT("'"&LOOKUP($B$5,TblA)&"'!B6:J36"), 0, MATCH(B7,INDIRECT("'"&LOOKUP($B$5,TblA)&"'!B5:J5"),0) )*1, 0 ), MATCH( 1, IF( 1*LEFT( INDEX( INDIRECT("'"&LOOKUP($B$5,TblA)&"'!B6:J36"), 0, MATCH(B7,INDIRECT("'"&LOOKUP($B$5,TblA)&"'!B5:J5"),0) ), FIND( "-", INDEX( INDIRECT("'"&LOOKUP($B$5,TblA)&"'!B6:J36"), 0, MATCH(B7,INDIRECT("'"&LOOKUP($B$5,TblA)&"'!B5:J5"),0) ) )-1 )<=C7, 1, 0 )* IF( 1*MID( INDEX( INDIRECT("'"&LOOKUP($B$5,TblA)&"'!B6:J36"), 0, MATCH(B7,INDIRECT("'"&LOOKUP($B$5,TblA)&"'!B5:J5"),0) ), FIND( "-", INDEX( INDIRECT("'"&LOOKUP($B$5,TblA)&"'!B6:J36"), 0, MATCH(B7,INDIRECT("'"&LOOKUP($B$5,TblA)&"'!B5:J5"),0) ) )+1, 100 )>=C7, 1, 0 ) ,0 ) ) ) ) * =INDEX( INDIRECT("'"&LOOKUP($B$5,TblA)&"'!A6:A36"), IFERROR( MATCH( C7, INDEX( INDIRECT("'"&LOOKUP($B$5,TblA)&"'!B6:J36"), 0, MATCH(B7,INDIRECT("'"&LOOKUP($B$5,TblA)&"'!B5:J5"),0) )*1, 0 ), MATCH( 1, IF( 1*LEFT( INDEX( INDIRECT("'"&LOOKUP($B$5,TblA)&"'!B6:J36"), 0, MATCH(B7,INDIRECT("'"&LOOKUP($B$5,TblA)&"'!B5:J5"),0) ), FIND( "-", INDEX( INDIRECT("'"&LOOKUP($B$5,TblA)&"'!B6:J36"), 0, MATCH(B7,INDIRECT("'"&LOOKUP($B$5,TblA)&"'!B5:J5"),0) ) )-1 )<=C7, 1, 0 )* IF( 1*MID( INDEX( INDIRECT("'"&LOOKUP($B$5,TblA)&"'!B6:J36"), 0, MATCH(B7,INDIRECT("'"&LOOKUP($B$5,TblA)&"'!B5:J5"),0) ), FIND( "-", INDEX( INDIRECT("'"&LOOKUP($B$5,TblA)&"'!B6:J36"), 0, MATCH(B7,INDIRECT("'"&LOOKUP($B$5,TblA)&"'!B5:J5"),0) ) )+1, 100 )>=C7, 1, 0 ) ,0 ) ) ) 

La version à une seule ligne …

 =INDEX(INDIRECT("'"&LOOKUP($B$5,TblA)&"'!A6:A36"),IFERROR(MATCH(C7,INDEX(INDIRECT("'"&LOOKUP($B$5,TblA)&"'!B6:J36"),0,MATCH(B7,INDIRECT("'"&LOOKUP($B$5,TblA)&"'!B5:J5"),0))*1,0),MATCH(1,IF(1*LEFT(INDEX(INDIRECT("'"&LOOKUP($B$5,TblA)&"'!B6:J36"),0,MATCH(B7,INDIRECT("'"&LOOKUP($B$5,TblA)&"'!B5:J5"),0)),FIND("-",INDEX(INDIRECT("'"&LOOKUP($B$5,TblA)&"'!B6:J36"),0,MATCH(B7,INDIRECT("'"&LOOKUP($B$5,TblA)&"'!B5:J5"),0)))-1)<=C7,1,0)*IF(1*MID(INDEX(INDIRECT("'"&LOOKUP($B$5,TblA)&"'!B6:J36"),0,MATCH(B7,INDIRECT("'"&LOOKUP($B$5,TblA)&"'!B5:J5"),0)),FIND("-",INDEX(INDIRECT("'"&LOOKUP($B$5,TblA)&"'!B6:J36"),0,MATCH(B7,INDIRECT("'"&LOOKUP($B$5,TblA)&"'!B5:J5"),0)))+1,100)>=C7,1,0),0))) 

Vous pouvez remarquer qu'il existe des blocs répétitifs, à savoir:

 INDIRECT("'"&LOOKUP($B$5,TblA)&"'!B6:J36") 

Pour le nom de la feuille;

 INDEX( INDIRECT("'"&LOOKUP($B$5,TblA)&"'!B6:J36"), 0, MATCH(B7,INDIRECT("'"&LOOKUP($B$5,TblA)&"'!B5:J5"),0) ) 

Quel est un bloc plus grand pour rendre la formule un peu plus flexible (elle choisit automatiquement la colonne correcte, par exemple si vous changez B8 Exclusion en Col1 , la formule s'ajuste automatiquement)

Si j'appelle la première Sheet et la deuxième Column , elle devient beaucoup plus courte et peut-être plus facile à comprendre:

 =INDEX( Sheet, IFERROR( MATCH( C7, Column*1, 0 ), MATCH( 1, IF( 1*LEFT( Column, FIND( "-", Column )-1 )<=C7, 1, 0 )* IF( 1*MID( Column, FIND( "-", Column )+1, 100 )>=C7, 1, 0 ) ,0 ) ) ) ) * =INDEX( Sheet, IFERROR( MATCH( C7, Column*1, 0 ), MATCH( 1, IF( 1*LEFT( Column, FIND( "-", Column )-1 )<=C7, 1, 0 )* IF( 1*MID( Column, FIND( "-", Column )+1, 100 )>=C7, 1, 0 ) ,0 ) ) ) 

Ou

 =INDEX(Sheet,IFERROR(MATCH(C7,Column*1,0),MATCH(1,IF(1*LEFT(Column,FIND("-",Column)-1)<=C7,1,0)*IF(1*MID(Column,FIND("-",Column)+1,100)>=C7,1,0),0))) 

Disclaimer: Je ne sais pas s'il y a un moyen de rendre cela encore plus court, mais je suppose que tant qu'il fonctionne maintenant ^^

Vous pouvez download votre feuille mise à jour ici .

---

Explication:

Comme je l'ai déjà mentionné, la formule est basée sur plusieurs petits et quelques répétitions de celles-ci.

 INDIRECT("'"&LOOKUP($B$5,TblA)&"'!B6:J36") 

Comme vous le savez déjà (c'est une variante d'une partie de votre propre formule), cela donne la zone contenant tous les différents âges. En l'utilisant et ci-dessous, nous obtenons ceci:

 INDEX( INDIRECT('"&LOOKUP($B$5,TblA)&"'!B6:J36"), 0, MATCH(B7,INDIRECT("'"&LOOKUP($B$5,TblA)&"'!B5:J5"),0) ) 

Dans:

 INDEX( 'Sheet'!B6:J36, 0, MATCH(B7,'Sheet'!B5:J5,0) ) 

L'indice affichera donc la gamme 'Sheet'!B6:J36 , 0 indique qu'il prendra n'importe quelle colonne et MATCH(B7,'Sheet'!B5:J5,0) renvoie la n- ième colonne en prenant la valeur de B7 (dans le cas de votre feuille de calcul, Col1 ) et en regardant 'Sheet'!B5:J5 qui donne 1 . Ce qui précède renverra ainsi la gamme 'Sheet'!B6:B36 . Allons-en dans la formule:

 =INDEX( 'Sheet'!A6:A36, IFERROR( MATCH( C7, 'Sheet'!B6:B36*1, 0 ), MATCH( 1, IF( 1*LEFT( 'Sheet'!B6:B36, FIND( "-", 'Sheet'!B6:B36 )-1 )<=C7, 1, 0 )* IF( 1*MID( 'Sheet'!B6:B36, FIND( "-", 'Sheet'!B6:B36 )+1, 100 )>=C7, 1, 0 ) ,0 ) ) ) ) * =INDEX( 'Sheet'!A6:A36, IFERROR( MATCH( C7, 'Sheet'!B6:B36*1, 0 ), MATCH( 1, IF( 1*LEFT( 'Sheet'!B6:B36, FIND( "-", 'Sheet'!B6:B36 )-1 )<=C7, 1, 0 )* IF( 1*MID( 'Sheet'!B6:B36, FIND( "-", 'Sheet'!B6:B36 )+1, 100 )>=C7, 1, 0 ) ,0 ) ) ) 

Cette formule est elle-même une formule INDEX géante, avec la gamme 'Sheet'!A6:A36 et numéro de ligne comme grand groupe IFERROR . La première partie de l' IFERROR() est évaluée en premier:

 MATCH( C7, 'Sheet'!B6:B36*1, 0 ) 

Cela devrait être assez facile à comprendre. Il search le score brut (de C7 ) dans la gamme que nous avons obtenue plus tôt, les fois 1 pour convertir tout en nombre (vous ne pouvez pas searchr des numbers et du text et attendre une correspondance). Donc, s'il y a une correspondance exacte d'un nombre, il renverra le nombre de lignes du score brut trouvé et l'alimentera à INDEX() . Par exemple, si la première ligne est returnnée, nous obtenons:

 =INDEX('Sheet'!A6:A36,1) 

Qu'est ce que 'Sheet'!B6 . Si toutefois il n'y a pas de correspondance (c'est-à-dire que le score brut ne peut pas être trouvé), MATCH renverra une erreur. Et c'est alors que la deuxième partie de l' IFERROR entre en jeu:

 MATCH( 1, IF( 1*LEFT( 'Sheet'!B6:B36, FIND( "-", 'Sheet'!B6:B36 )-1 )<=C7, 1, 0 )* IF( 1*MID( 'Sheet'!B6:B36, FIND( "-", 'Sheet'!B6:B36 )+1, 100 )>=C7, 1, 0 ) ,0 ) ) * MATCH( 1, IF( 1*LEFT( 'Sheet'!B6:B36, FIND( "-", 'Sheet'!B6:B36 )-1 )<=C7, 1, 0 )* IF( 1*MID( 'Sheet'!B6:B36, FIND( "-", 'Sheet'!B6:B36 )+1, 100 )>=C7, 1, 0 ) ,0 ) 

Ce MATCH tente de find 1 dans ce qui semble être deux IF s; le premier étant:

 IF( 1*LEFT('Sheet'!B6:B36,FIND("-",'Sheet'!B6:B36)-1)<=C7 , 1 , 0) 

FIND("-",'Sheet'!B6:B36)-1 obtient la position du dernier caractère avant le - dans la colonne 'Sheet'!B6:B36 .

Avec ces valeurs, cette FIND renverrait:

 12-13 -> 2 145-155 -> 3 1567-1865 -> 4 

L' IF devient ainsi:

 IF( 1*LEFT('Sheet'!B6:B36,{2,3,4})<=C7 , 1 , 0) 

Remarquez les entretoises ici; ils indiquent un tableau et c'est pourquoi il s'agit d'une formule de tableau. LEFT puis extrait tous les personnages avant le - (souvenez-vous de votre autre question, j'ai répondu avec une technique très similaire à celle-ci):

 12-13 -> 2 -> 12 145-155 -> 3 -> 145 1567-1865 -> 4 -> 1567 

Lequel est…

 IF( 1*{12,145,1567}<=C7 , 1 , 0) 

Encore une fois, 1* convertit en nombres réels parce que LEFT est le return par défaut des caractères de text. Il est important ici de le faire parce que nous allons utiliser le comparateur <= , de sorte que, si la valeur à gauche de C7 (le score brut), alors l'IF devrait returnner 1 , sinon, il devrait returnner 0 . Disons que le score brut était de 154 . Les résultats seraient:

 IF( {12,145,1567}<=154 , 1 , 0) IF( {TRUE,TRUE,FALSE} , 1 , 0) {1,1,0} 

Je me suis rendu count que la formule peut être un peu plus courte xD. De toute façon, nous verrons plus tard. La prochaine IF se comporte de la même manière, mais vérifie la valeur à droite de la - :

 IF( 1*MID('Sheet'!B6:B36,FIND("-",'Sheet'!B6:B36)+1,100)>=C7 , 1 , 0) 

Avec … FIND MID ('Sheet'! B6: B36, X, 100) 12-13 -> 4 -> 13 145-155 -> 5 -> 155 1567-1865 -> 6 -> 1865

Vous pouvez constater que cette formule cessera de fonctionner si vous avez quelque chose de plus de 100 caractères. Quoi qu'il en soit, la IF devient ainsi:

 IF( {13,155,1865}>=154 , 1 , 0) IF( {FALSE,TRUE,TRUE} , 1 , 0) {0,1,1} 

Maintenant que nous en avons, le MATCH d'avant devient:

 MATCH( 1 , {1,1,0}*{0,1,1} , 0) 

Quelques mathématiques simples font cela dans:

 MATCH( 1 , {0,1,0} , 0) 

Et quelle est la position du 1 là-dedans? C'est vrai, position 2!

Notre formule originale devient:

 =INDEX( 'Sheet'!A6:A36 , IFERROR( #Error! , 2 ) ) 

Donc, si rien n'a été trouvé au début, il renverra une erreur ( #N/A dans ce cas) et renvoyez 2 . =INDEX( 'Sheet'!A6:A36 , 2 ) donne 'Sheet'!A7 .

---

Et la version légèrement plus courte est:

 =INDEX(INDIRECT("'"&LOOKUP($B$5,TblA)&"'!A6:A36"),IFERROR(MATCH(C7,INDEX(INDIRECT("'"&LOOKUP($B$5,TblA)&"'!B6:J36"),0,MATCH(B7,INDIRECT("'"&LOOKUP($B$5,TblA)&"'!B5:J5"),0))*1,0),MATCH(1,(1*LEFT(INDEX(INDIRECT("'"&LOOKUP($B$5,TblA)&"'!B6:J36"),0,MATCH(B7,INDIRECT("'"&LOOKUP($B$5,TblA)&"'!B5:J5"),0)),FIND("-",INDEX(INDIRECT("'"&LOOKUP($B$5,TblA)&"'!B6:J36"),0,MATCH(B7,INDIRECT("'"&LOOKUP($B$5,TblA)&"'!B5:J5"),0)))-1)<=C7)*(1*MID(INDEX(INDIRECT("'"&LOOKUP($B$5,TblA)&"'!B6:J36"),0,MATCH(B7,INDIRECT("'"&LOOKUP($B$5,TblA)&"'!B5:J5"),0)),FIND("-",INDEX(INDIRECT("'"&LOOKUP($B$5,TblA)&"'!B6:J36"),0,MATCH(B7,INDIRECT("'"&LOOKUP($B$5,TblA)&"'!B5:J5"),0)))+1,100)>=C7),0))) 

J'ai en fait supprimé les IF internes, car (a>b)*(c>b) renvoie déjà 0s et 1s puisque TRUE multiplié par TRUE donne 1 dans excel.

Dans Excel 2007, les formules renvoient beaucoup d'avertissements de reference circulaires, il est donc utile d'append une balise pour votre version Excel. Mine est Excel 2007 mais avec elle, les résultats que vous voulez semblent réalisables comme suit:

1. Pour raccourcir les formules et réduire le calcul, j'ai ajouté dans Report C5 "Table" et dans D5 =VLOOKUP(B5,TblA,2,1) .
2. J'ai également inséré une colonne immédiatement à droite de ColumnH ("ColD") dans 5.2.0 à 5.7.30 et appliqué Text To Columns sur la colonne H, avec - comme délimiteur.

3. J'ai ensuite appliqué au rapport E7 et copié en E15:

   =INDEX(INDIRECT("'"&D$5&"'!A6:A36"),MATCH(C7,INDIRECT("'"&D$5&"'!"&CHAR(ROW()+59)&"6:"&CHAR(ROW()+59)&"36"),0))

   et a ajusté les 59 s à 60 s dans les trois dernières lignes. Un tel ajustement ne serait pas nécessaire si ColumnI était déplacé assez loin vers la droite.

4. Dans E13, j'ai changé la correspondance de l'exacte à la suivante supérieure (finale 0 à -1 ).

5. Pour les figures, j'ai sortingché et modifié K23 en 5.2.0 à 5.7.30 à 22 du 21-22, mais une telle bande pourrait, pour d'autres colonnes, être traitée de la même manière que pour ColD.