{"id":10382,"date":"2022-11-12T00:58:29","date_gmt":"2022-11-11T23:58:29","guid":{"rendered":"https:\/\/www.auditsi.eu\/?p=10382"},"modified":"2022-11-20T22:07:58","modified_gmt":"2022-11-20T21:07:58","slug":"recherchex-la-nouvelle-fonction-de-recherche-de-donnee-dexcel-qui-ringardise-recherchev","status":"publish","type":"post","link":"https:\/\/www.auditsi.eu\/?p=10382","title":{"rendered":"RECHERCHEX, la nouvelle fonction de recherche de donn\u00e9es d&#8217;Excel qui ringardise RECHERCHEV"},"content":{"rendered":"<p style=\"text-align: justify;\"><a href=\"https:\/\/www.auditsi.eu\/?p=8952\">RECHERCHEV<\/a> est \u00e0 ce jour l&#8217;une des fonctionnalit\u00e9 d&#8217;Excel les plus utilis\u00e9es (au m\u00eame titre que les filtres, les <a href=\"https:\/\/www.auditsi.eu\/?tag=tcd\">tableaux crois\u00e9s dynamiques<\/a>, les <a href=\"https:\/\/www.auditsi.eu\/?p=8383\">formats conditionnels<\/a>, les <a href=\"https:\/\/www.auditsi.eu\/?p=4363\">calculs conditionnels<\/a>&#8230;). L&#8217;arriv\u00e9e r\u00e9cente de RECHERCHEX (en un seul mot) risque bien de mettre RECHERCHEV au placard&#8230; et c&#8217;est tant mieux !<\/p>\n<p style=\"text-align: justify;\">En effet, RECHERCHEX s&#8217;affranchit des d\u00e9fauts de la fonction RECHERCHEV.<\/p>\n<p style=\"text-align: justify;\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-10441\" src=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2022\/11\/RECHERCHEX-1.png\" alt=\"RECHERCHEX 1\" width=\"469\" height=\"397\" srcset=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2022\/11\/RECHERCHEX-1.png 469w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2022\/11\/RECHERCHEX-1-300x254.png 300w\" sizes=\"auto, (max-width: 469px) 100vw, 469px\" \/><\/p>\n<p style=\"text-align: justify;\">La fonction RECHERCHEX cherche une valeur dans un tableau puis retourne la valeur correspondante (\u00e0 la m\u00eame position) dans une autre colonne ; cette autre colonne peut \u00eatre situ\u00e9e dans un second tableau. La recherche peut \u00eatre verticale ou horizontale.<\/p>\n<p style=\"text-align: justify;\">Syntaxe :<\/p>\n<p style=\"text-align: center;\">=RECHERCHEX(valeur_recherch\u00e9e;plage_recherche;plage_r\u00e9sultat;[retour_absence_r\u00e9sultat];[correspondance];[mode])<\/p>\n<p>Arguments obligatoires :<\/p>\n<ul>\n<li style=\"text-align: justify;\">valeur_recherch\u00e9e : valeur recherch\u00e9e<\/li>\n<li style=\"text-align: justify;\">plage_recherche : plage de cellules comprenant les clefs de recherche<\/li>\n<li style=\"text-align: justify;\">plage_r\u00e9sultat : plage de cellules comprenant les valeurs \u00e0 restituer<\/li>\n<li style=\"text-align: justify;\">Si la plage comprend plusieurs colonnes, la formule de calcul sera propag\u00e9e automatiquement pour afficher tous les r\u00e9sultats<\/li>\n<\/ul>\n<p style=\"text-align: justify;\">Arguments facultatifs :<\/p>\n<ul style=\"text-align: justify;\">\n<li>Retour_absence_r\u00e9sultat : valeur \u00e0 retourner si aucun r\u00e9sultat trouv\u00e9 pour \u00e9viter le <a href=\"https:\/\/www.auditsi.eu\/?p=8748\">message d\u2019erreur #N\/A<\/a>. Cet argument permet d\u2019imbriquer des RECHERCHEX successifs ou des calculs alternatifs (cet argument fonctionne de la m\u00eame mani\u00e8re qu&#8217;avec la <a href=\"https:\/\/www.auditsi.eu\/?p=8758\">fonction SIERREUR<\/a> ; exemple avec <a href=\"https:\/\/www.auditsi.eu\/?p=8952\">RECHERCHEV<\/a>).<\/li>\n<li>Correspondance :\n<ul>\n<li>0 (par d\u00e9faut) : correspondance exacte (si aucune correspondance : une erreur #N\/A ou la valeur Si_non_trouv\u00e9)<\/li>\n<li>-1 : si aucune correspondance exacte, r\u00e9sultat = valeur inf\u00e9rieure suivante<\/li>\n<li>1 : si aucune correspondance exacte,&nbsp; r\u00e9sultat = valeur sup\u00e9rieure suivante<\/li>\n<li>2 : correspondance avec <a href=\"https:\/\/www.auditsi.eu\/?p=10420\">caract\u00e8re g\u00e9n\u00e9rique<\/a> (*, ?, ~)<\/li>\n<\/ul>\n<\/li>\n<li>Mode :\n<ul>\n<li>1 (par d\u00e9faut) : recherche en commen\u00e7ant par le d\u00e9but (par d\u00e9faut)<\/li>\n<li>-1 : recherche en commen\u00e7ant par la fin<\/li>\n<li>2 : recherche binaire croissante<\/li>\n<li>-2 : recherche binaire d\u00e9croissante<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<p style=\"text-align: justify;\">Comparaison RECHERCHEX \/ RECHERCHEV :<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-10442\" src=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2022\/11\/RECHERCHEX-V.png\" alt=\"RECHERCHEX V\" width=\"691\" height=\"104\" srcset=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2022\/11\/RECHERCHEX-V.png 691w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2022\/11\/RECHERCHEX-V-300x45.png 300w\" sizes=\"auto, (max-width: 691px) 100vw, 691px\" \/><\/p>\n<p style=\"text-align: justify;\">Plage de r\u00e9sultat comprenant plusieurs colonnes : La formule se duplique automatiquement pour afficher l\u2019ensemble du r\u00e9sultat<\/p>\n<p style=\"text-align: justify;\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-10443\" src=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2022\/11\/RECHERCHEX-plusieurs-colonnes.png\" alt=\"RECHERCHEX plusieurs colonnes\" width=\"691\" height=\"43\" srcset=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2022\/11\/RECHERCHEX-plusieurs-colonnes.png 691w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2022\/11\/RECHERCHEX-plusieurs-colonnes-300x19.png 300w\" sizes=\"auto, (max-width: 691px) 100vw, 691px\" \/><\/p>\n<p style=\"text-align: justify;\">Pour transposer les r\u00e9sultats avec la <a href=\"https:\/\/www.auditsi.eu\/?p=10426\">fonction TRANSPOSE<\/a> : =TRANSPOSE(RECHERCHEX(\u2026))<\/p>\n<p style=\"text-align: justify;\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-10444\" src=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2022\/11\/RECHERCHEX-transposition.png\" alt=\"RECHERCHEX transposition\" width=\"691\" height=\"63\" srcset=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2022\/11\/RECHERCHEX-transposition.png 691w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2022\/11\/RECHERCHEX-transposition-300x27.png 300w\" sizes=\"auto, (max-width: 691px) 100vw, 691px\" \/><\/p>\n<p style=\"text-align: justify;\">Utilisation des <a href=\"https:\/\/www.auditsi.eu\/?p=10420\">caract\u00e8res g\u00e9n\u00e9riques (&#8220;jokers&#8221;)<\/a> :<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-10445\" src=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2022\/11\/RECHERCHEX-caracteres-generiques.png\" alt=\"RECHERCHEX caract\u00e8res g\u00e9n\u00e9riques\" width=\"691\" height=\"84\" srcset=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2022\/11\/RECHERCHEX-caracteres-generiques.png 691w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2022\/11\/RECHERCHEX-caracteres-generiques-300x36.png 300w\" sizes=\"auto, (max-width: 691px) 100vw, 691px\" \/><\/p>\n<p style=\"text-align: justify;\">RECHERCHEX avec plages disjointes :<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-10446\" src=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2022\/11\/RECHERCHEX-2.png\" alt=\"RECHERCHEX 2\" width=\"386\" height=\"364\" srcset=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2022\/11\/RECHERCHEX-2.png 386w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2022\/11\/RECHERCHEX-2-300x283.png 300w\" sizes=\"auto, (max-width: 386px) 100vw, 386px\" \/><\/p>\n<p style=\"text-align: justify;\"><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-10447 aligncenter\" src=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2022\/11\/RECHERCHEX-plages-disjointes.png\" alt=\"RECHERCHEX plages disjointes\" width=\"691\" height=\"123\" srcset=\"https:\/\/www.auditsi.eu\/wp-content\/uploads\/2022\/11\/RECHERCHEX-plages-disjointes.png 691w, https:\/\/www.auditsi.eu\/wp-content\/uploads\/2022\/11\/RECHERCHEX-plages-disjointes-300x53.png 300w\" sizes=\"auto, (max-width: 691px) 100vw, 691px\" \/><\/p>\n<p style=\"text-align: justify;\">___<\/p>\n<p style=\"text-align: justify;\">Approfondir le sujet : <a href=\"https:\/\/www.auditsi.eu\/?cat=2765\">Analyse de donn\u00e9es et automatisation<\/a> \/ <a href=\"https:\/\/www.auditsi.eu\/?glossary=excel\">Tout savoir sur Excel<\/a>&nbsp;\/&nbsp;<a title=\"S\u00e9rie d'articles Ma\u00eetriser Excel\" href=\"https:\/\/www.auditsi.eu\/?p=4475\">D\u00e9couvrir la s\u00e9rie d\u2019articles Ma\u00eetriser Excel<\/a><\/p>\n<div id=\"sconnect-is-installed\" style=\"display: none; text-align: justify;\">2.13.0.0<\/div>\n<div id=\"sconnect-is-installed\" style=\"display: none; text-align: justify;\">2.13.0.0<\/div>\n<div id=\"sconnect-is-installed\" style=\"display: none;\">2.13.0.0<\/div>\n<div id=\"sconnect-is-installed\" style=\"display: none;\">2.13.0.0<\/div>\n<div id=\"sconnect-is-installed\" style=\"display: none;\">2.13.0.0<\/div>\n<div id=\"sconnect-is-installed\" style=\"display: none;\">2.13.0.0<\/div>\n<div id=\"sconnect-is-installed\" style=\"display: none;\">2.13.0.0<\/div>\n<div id=\"sconnect-is-installed\" style=\"display: none;\">2.13.0.0<\/div>\n<div id=\"sconnect-is-installed\" style=\"display: none;\">2.13.0.0<\/div>\n<div style=\"padding-bottom:20px; padding-top:10px;\" class=\"hupso-share-buttons\"><!-- Hupso Share Buttons - https:\/\/www.hupso.com\/share\/ --><a class=\"hupso_counters\" href=\"https:\/\/www.hupso.com\/share\/\"><img decoding=\"async\" src=\"https:\/\/static.hupso.com\/share\/buttons\/lang\/fr\/share-small.png\" style=\"border:0px; padding-top:2px; float:left;\" alt=\"Share Button\"\/><\/a><script type=\"text\/javascript\">var hupso_services_c=new Array(\"twitter\",\"facebook_like\",\"facebook_send\",\"email\",\"print\",\"linkedin\");var hupso_counters_lang = \"fr_FR\";var hupso_image_folder_url = \"\";var hupso_twitter_via=\"BenoitRiviere14\";var hupso_url_c=\"\";var hupso_title_c=\"RECHERCHEX%2C%20la%20nouvelle%20fonction%20de%20recherche%20de%20donn%C3%A9es%20d%27Excel%20qui%20ringardise%20RECHERCHEV\";<\/script><script type=\"text\/javascript\" src=\"https:\/\/static.hupso.com\/share\/js\/counters.js\"><\/script><!-- Hupso Share Buttons --><\/div>","protected":false},"excerpt":{"rendered":"<p>RECHERCHEV est \u00e0 ce jour l&#8217;une des fonctionnalit\u00e9 d&#8217;Excel les plus utilis\u00e9es (au m\u00eame titre que les filtres, les tableaux crois\u00e9s dynamiques, les formats conditionnels, les calculs conditionnels&#8230;). L&#8217;arriv\u00e9e r\u00e9cente de RECHERCHEX (en un seul mot) risque bien de mettre RECHERCHEV au placard&#8230; et c&#8217;est tant mieux ! En effet, RECHERCHEX s&#8217;affranchit des d\u00e9fauts de &#8230;<\/p>\n<p><a href=\"https:\/\/www.auditsi.eu\/?p=10382\" class=\"more-link\">Continue reading &lsquo;RECHERCHEX, la nouvelle fonction de recherche de donn\u00e9es d&#8217;Excel qui ringardise RECHERCHEV&rsquo; &raquo;<\/a><\/p>\n<div style=\"padding-bottom:20px; padding-top:10px;\" class=\"hupso-share-buttons\"><!-- Hupso Share Buttons - https:\/\/www.hupso.com\/share\/ --><a class=\"hupso_counters\" href=\"https:\/\/www.hupso.com\/share\/\"><img src=\"https:\/\/static.hupso.com\/share\/buttons\/lang\/fr\/share-small.png\" style=\"border:0px; padding-top:2px; float:left;\" alt=\"Share Button\"\/><\/a><script type=\"text\/javascript\">var hupso_services_c=new Array(\"twitter\",\"facebook_like\",\"facebook_send\",\"email\",\"print\",\"linkedin\");var hupso_counters_lang = \"fr_FR\";var hupso_image_folder_url = \"\";var hupso_twitter_via=\"BenoitRiviere14\";var hupso_url_c=\"\";var hupso_title_c=\"RECHERCHEX%2C%20la%20nouvelle%20fonction%20de%20recherche%20de%20donn%C3%A9es%20d%27Excel%20qui%20ringardise%20RECHERCHEV\";<\/script><script type=\"text\/javascript\" src=\"https:\/\/static.hupso.com\/share\/js\/counters.js\"><\/script><!-- Hupso Share Buttons --><\/div>","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"advanced_seo_description":"","jetpack_seo_html_title":"","jetpack_seo_noindex":false,"ngg_post_thumbnail":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","_links_to":"","_links_to_target":""},"categories":[48,1326],"tags":[2024,2025,688,2774,2769],"class_list":["post-10382","post","type-post","status-publish","format-standard","hentry","category-excel","category-maitriser-excel","tag-caractere-generique","tag-joker","tag-recherchev","tag-recherchex","tag-transpose"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_likes_enabled":false,"_links":{"self":[{"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/posts\/10382","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=10382"}],"version-history":[{"count":10,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/posts\/10382\/revisions"}],"predecessor-version":[{"id":10450,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=\/wp\/v2\/posts\/10382\/revisions\/10450"}],"wp:attachment":[{"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=10382"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=10382"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.auditsi.eu\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=10382"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}