{"id":40,"date":"2023-05-18T04:32:44","date_gmt":"2023-05-18T04:32:44","guid":{"rendered":"https:\/\/xlsx.ro\/?p=40"},"modified":"2023-11-30T12:07:50","modified_gmt":"2023-11-30T12:07:50","slug":"cautari-cu-vlookup-vlookup-si-xlookup","status":"publish","type":"post","link":"https:\/\/xlsx.ro\/?p=40","title":{"rendered":"C\u0103ut\u0103ri cu vlookup, hlookup \u0219i xlookup"},"content":{"rendered":"\n<p>Func\u021biile VLOOKUP, HLOOKUP \u0219i XLOOKUP sunt trei func\u021bii puternice \u0219i utile \u00een Excel, utilizate pentru a c\u0103uta \u0219i extrage informa\u021bii dintr-un tabel sau o gam\u0103 de celule, pe baza unei anumite valori. Fiecare dintre aceste func\u021bii are propriile sale caracteristici \u0219i modalit\u0103\u021bi de utilizare, a\u0219a c\u0103 s\u0103 le analiz\u0103m pe r\u00e2nd, \u00eempreun\u0103 cu exemple practice.<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>VLOOKUP (Vertical Lookup): Func\u021bia VLOOKUP este utilizat\u0103 pentru a c\u0103uta o valoare \u00een prima coloan\u0103 a unei matrice \u0219i pentru a returna valoarea corespunz\u0103toare dintr-o alt\u0103 coloan\u0103 din aceea\u0219i matrice. Sintaxa func\u021biei VLOOKUP este urm\u0103toarea:<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-preformatted\"><code>VLOOKUP(value, table, col_index, range_lookup)<\/code><\/pre>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>value<\/code> reprezint\u0103 valoarea pe care o c\u0103ut\u0103m \u00een prima coloan\u0103 a matricei.<\/li>\n\n\n\n<li><code>table<\/code> este intervalul de celule care reprezint\u0103 matricea \u00een care c\u0103ut\u0103m.<\/li>\n\n\n\n<li><code>col_index<\/code> este num\u0103rul coloanei din matrice din care dorim s\u0103 extragem rezultatul.<\/li>\n\n\n\n<li><code>range_lookup<\/code> este un parametru op\u021bional, care specific\u0103 dac\u0103 dorim o c\u0103utare aproximativ\u0103 (<code>TRUE<\/code>) sau exact\u0103 (<code>FALSE<\/code>).<\/li>\n<\/ul>\n\n\n\n<p>Exemplu: S\u0103 presupunem c\u0103 avem o list\u0103 cu informa\u021bii despre produse \u0219i pre\u021burile lor \u00eentr-un tabel. Dorim s\u0103 g\u0103sim pre\u021bul unui anumit produs, av\u00e2nd la dispozi\u021bie numele produsului. Utiliz\u0103m func\u021bia VLOOKUP pentru a rezolva aceast\u0103 problem\u0103.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">=<code>VLOOKUP(\"Laptop\", A2:B10, 2, FALSE)<\/code><\/pre>\n\n\n\n<p>Aceast\u0103 formul\u0103 va c\u0103uta valoarea &#8222;Laptop&#8221; \u00een prima coloan\u0103 a intervalului A2:B10 \u0219i va returna valoarea din a doua coloan\u0103 corespunz\u0103toare acestei c\u0103ut\u0103ri, adic\u0103 pre\u021bul laptopului.<\/p>\n\n\n\n<ol class=\"wp-block-list\" start=\"2\">\n<li>HLOOKUP (Horizontal Lookup): Func\u021bia HLOOKUP este similar\u0103 func\u021biei VLOOKUP, cu excep\u021bia faptului c\u0103 c\u0103utarea se efectueaz\u0103 pe r\u00e2ndurile orizontale ale matricei, \u00een loc de coloanele verticale. Sintaxa func\u021biei HLOOKUP este urm\u0103toarea:<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-preformatted\"><code>HLOOKUP(value, table, row_index, range_lookup)<\/code><\/pre>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>value<\/code> reprezint\u0103 valoarea pe care o c\u0103ut\u0103m \u00een primul r\u00e2nd al matricei.<\/li>\n\n\n\n<li><code>table<\/code> este intervalul de celule care reprezint\u0103 matricea \u00een care c\u0103ut\u0103m.<\/li>\n\n\n\n<li><code>row_index<\/code> este num\u0103rul r\u00e2ndului din matrice din care dorim s\u0103 extragem rezultatul.<\/li>\n\n\n\n<li><code>range_lookup<\/code> este un parametru op\u021bional, care specific\u0103 dac\u0103 dorim o c\u0103utare aproximativ\u0103 (<code>TRUE<\/code>) sau exact\u0103 (<code>FALSE<\/code>).<\/li>\n<\/ul>\n\n\n\n<p>Exemplu: S\u0103 presupunem c\u0103 avem un tabel cu informa\u021bii despre elevi \u0219i notele lor la diferite teste. Dorim s\u0103 g\u0103sim nota unui elev la un anumit test, av\u00e2nd la dispozi\u021bie numele elevului. Utiliz\u0103m func\u021bia HLOOKUP pentru a rezolva aceast\u0103 problem\u0103.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><code>=HLOOKUP(\"John\", A2:F10, 2, FALSE)<\/code><\/pre>\n\n\n\n<p>Aceast\u0103 formul\u0103 va c\u0103uta valoarea &#8222;John&#8221; \u00een primul r\u00e2nd al intervalului A2:F10 \u0219i va returna valoarea din al doilea r\u00e2nd corespunz\u0103toare acestei c\u0103ut\u0103ri, adic\u0103 nota lui John la test.<\/p>\n\n\n\n<ol class=\"wp-block-list\" start=\"3\">\n<li>XLOOKUP (Extended Lookup): Func\u021bia XLOOKUP este o func\u021bie nou\u0103 introdus\u0103 \u00een Excel \u0219i reprezint\u0103 o versiune \u00eembun\u0103t\u0103\u021bit\u0103 a func\u021biilor VLOOKUP \u0219i HLOOKUP. XLOOKUP ofer\u0103 o flexibilitate mai mare \u0219i poate c\u0103uta \u00een orice direc\u021bie (vertical\u0103 \u0219i orizontal\u0103), precum \u0219i \u00een st\u00e2nga \u0219i \u00een dreapta valorii de referin\u021b\u0103. Sintaxa func\u021biei XLOOKUP este urm\u0103toarea:<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-preformatted\"><code>XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])<\/code><\/pre>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>lookup_value<\/code> reprezint\u0103 valoarea pe care o c\u0103ut\u0103m \u00een intervalul de c\u0103utare.<\/li>\n\n\n\n<li><code>lookup_array<\/code> este intervalul de celule \u00een care c\u0103ut\u0103m valoarea.<\/li>\n\n\n\n<li><code>return_array<\/code> este intervalul de celule din care extragem rezultatul.<\/li>\n\n\n\n<li><code>if_not_found<\/code> este un parametru op\u021bional care specific\u0103 ce s\u0103 se \u00eent\u00e2mple dac\u0103 valoarea c\u0103utat\u0103 nu este g\u0103sit\u0103.<\/li>\n\n\n\n<li><code>match_mode<\/code> este un parametru op\u021bional care specific\u0103 modul de potrivire a valorii c\u0103utate.<\/li>\n\n\n\n<li><code>search_mode<\/code> este un parametru op\u021bional care specific\u0103 modul de c\u0103utare.<\/li>\n<\/ul>\n\n\n\n<p>Exemplu: S\u0103 presupunem c\u0103 avem un tabel cu informa\u021bii despre angaja\u021bii unei companii \u0219i dorim s\u0103 g\u0103sim salariul unui anumit angajat, av\u00e2nd la dispozi\u021bie num\u0103rul de identificare al acestuia. Utiliz\u0103m func\u021bia XLOOKUP pentru a rezolva aceast\u0103 problem\u0103.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><code>=XLOOKUP(\"123456\", A2:A10, C2:C10, \"N\/A\", 0, 1)<\/code><\/pre>\n\n\n\n<p>Aceast\u0103 formul\u0103 va c\u0103uta valoarea &#8222;123456&#8221; \u00een intervalul A2:A10 \u0219i va returna valoarea din coloana C corespunz\u0103toare acestei c\u0103ut\u0103ri, adic\u0103 salariul angajatului cu num\u0103rul de identificare &#8222;123456&#8221;. Dac\u0103 valoarea nu este g\u0103sit\u0103, se va afi\u0219a mesajul &#8222;N\/A&#8221;. Match_mode este setat la 0 pentru o potrivire exact\u0103, iar search_mode este setat la 1 pentru o c\u0103utare \u00een ordine cresc\u0103toare.<\/p>\n\n\n\n<p>Acestea sunt doar c\u00e2teva exemple ale func\u021biilor VLOOKUP, HLOOKUP \u0219i XLOOKUP \u0219i modul \u00een care pot fi utilizate \u00een Excel pentru a extrage informa\u021bii relevante dintr-un tabel sau o gam\u0103 de celule. Cu ajutorul acestor func\u021bii, pute\u021bi realiza rapid \u0219i eficient c\u0103ut\u0103ri \u0219i extrageri de date \u00eentr-un mod structurat \u0219i convenabil.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Func\u021biile VLOOKUP, HLOOKUP \u0219i XLOOKUP sunt trei func\u021bii puternice \u0219i utile \u00een Excel, utilizate pentru a c\u0103uta \u0219i extrage informa\u021bii dintr-un tabel sau o gam\u0103 de celule, pe baza unei anumite valori. Fiecare dintre aceste func\u021bii are propriile sale caracteristici \u0219i modalit\u0103\u021bi de utilizare, a\u0219a c\u0103 s\u0103 le analiz\u0103m pe r\u00e2nd, \u00eempreun\u0103 cu exemple practice. [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","ast-disable-related-posts":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"set","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"footnotes":""},"categories":[13],"tags":[],"class_list":["post-40","post","type-post","status-publish","format-standard","hentry","category-excel"],"_links":{"self":[{"href":"https:\/\/xlsx.ro\/index.php?rest_route=\/wp\/v2\/posts\/40","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/xlsx.ro\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/xlsx.ro\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/xlsx.ro\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/xlsx.ro\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=40"}],"version-history":[{"count":3,"href":"https:\/\/xlsx.ro\/index.php?rest_route=\/wp\/v2\/posts\/40\/revisions"}],"predecessor-version":[{"id":50,"href":"https:\/\/xlsx.ro\/index.php?rest_route=\/wp\/v2\/posts\/40\/revisions\/50"}],"wp:attachment":[{"href":"https:\/\/xlsx.ro\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=40"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/xlsx.ro\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=40"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/xlsx.ro\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=40"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}