RechercheX

%3CLINGO-SUB%20id%3D%22lingo-sub-2990837%22%20slang%3D%22fr-FR%22%3EResearchX%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2990837%22%20slang%3D%22fr-FR%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3EThe%20use%20of%20the%20Xlookup%20(searchX)%20function%20is%20very%20useful%20and%20works%20perfectly%20in%20a%20%22%20Tabular%20Format%22.%3CBR%20%2F%3EHowever%2C%20I%20notice%20that%20in%20some%20%22Tables%22%20this%20formula%20does%20not%20work%20and%20the%20error%20message%20%22%23SPILL%22%20(%22%23EPARS%22)%20appears.%20This%20can%20be%20seen%20regardless%20of%20the%20size%20of%20these%20tables%20and%20their%20number%20of%20rows.%3CBR%20%2F%3EHowever%2C%20the%20%22tables%22%20where%20this%20formula%20works%20do%20not%20seem%20to%20be%20any%20different%20from%20the%20other%20%22tables%22.%3CBR%20%2F%3ECan%20you%20give%20me%20a%20lead%20to%20investigate%20the%20cause%20and%20be%20able%20to%20continue%20using%20the%20%22tables%22%3F%20I%20am%20working%20on%20Microsoft%20365.%3CBR%20%2F%3EThanks%20in%20advance%3C%2FP%3E%3CP%3EHello%20%3CBR%20%2F%3E%20Using%20the%20X%20search%20function%20(Xlookup)%20is%20very%20useful%20and%20works%20perfectly%20in%20%22cell%20ranges%22.%3CBR%20%2F%3EHowever%2C%20I%20notice%20that%20in%20some%20%22tables%22%20this%20formula%20does%20not%20work%20and%20the%20error%20message%20%22%23EPARS%22%20(%22%23SPILL%22)%20appears.%20This%20can%20be%20seen%20regardless%20of%20the%20size%20of%20these%20tables%20and%20their%20number%20of%20rows.%3CBR%20%2F%3EHowever%2C%20the%20%22tables%22%20where%20this%20formula%20works%20do%20not%20seem%20different%20from%20the%20other%20%22tables%22.%3CBR%20%2F%3ECan%20you%20give%20me%20a%20lead%20to%20look%20for%20the%20cause%20and%20be%20able%20to%20continue%20using%20the%20%22tables%22%3F%20I%20work%20on%20Microsoft%20365.%3CBR%20%2F%3EThanks%20in%20advance%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2990837%22%20slang%3D%22fr-FR%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2990883%22%20slang%3D%22en-US%22%3ERe%3A%20RechercheX%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2990883%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1225555%22%20target%3D%22_blank%22%3E%40Bruneau_Didier%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%5B%5BName%20%5D%5D%20refers%20to%20the%20entire%20column.%20Instead%2C%20use%3C%2FP%3E%0A%3CP%3E%3DXLOOKUP(%5B%40%5BName%20%5D%5D%2CTD_IDnumber%5BName%5D%2CTD_IDnumber%5BID%5D)%3C%2FP%3E%0A%3CP%3Eto%20refer%20to%20the%20name%20in%20the%20same%20row.%20In%20French%3A%3C%2FP%3E%0A%3CP%3E%3DRECHERCHEX(%5B%40%5BName%20%5D%5D%3BTD_IDnumber%5BName%5D%3BTD_IDnumber%5BID%5D)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello,

The use of the Xlookup (searchX) function is very useful and works perfectly in a " Tabular Format".
However, I notice that in some "Tables" this formula does not work and the error message "#SPILL"  ("#EPARS") appears. This can be seen regardless of the size of these tables and their number of rows.
However, the "tables" where this formula works do not seem to be any different from the other "tables".
Can you give me a lead to investigate the cause and be able to continue using the "tables"? I am working on Microsoft 365.
Thanks in advance

Bonjour
L'utilisation de la fonction rechercheX (Xlookup ) est très utile et fonctionne parfaitement dans les "plages de cellules".
Toutefois, je remarque que dans certains "tableaux", cette formule ne fonctionne pas et qu'il apparaît le message d'erreur " #EPARS " ("#SPILL") . Ceci peut se constater quelle que soit la taille de ces tableaux et leur nombre de lignes.
Toutefois, les "tableaux" où cette formule fonctionne ne semblent pas différents des autres "tableaux".
Pouvez-vous me donner une piste pour rechercher la cause et pouvoir continuer à utiliser les "tableaux"? Je travaille sur Microsoft 365.
Merci d'avance

2 Replies

@Bruneau_Didier 

[[Name ]] refers to the entire column. Instead, use

=XLOOKUP([@[Name ]],TD_IDnumber[Name],TD_IDnumber[ID])

to refer to the name in the same row. In French:

=RECHERCHEX([@[Name ]];TD_IDnumber[Name];TD_IDnumber[ID])

 

 

Hans,
Thanks a lot!
It works now.