Sa pamamagitan ng pagsasama-sama ng VLOOKUP function ng Excel sa COLUMN function, makakagawa ka ng lookup formula na nagbabalik ng maraming value mula sa isang row ng database o talahanayan ng data. Matutunan kung paano gumawa ng lookup formula na nagbabalik ng maraming value mula sa iisang talaan ng data.
Ang mga tagubilin sa artikulong ito ay nalalapat sa Excel 2019, 2016, 2013, 2010; at Excel para sa Microsoft 365.
Bottom Line
Ang lookup formula ay nangangailangan ng COLUMN function na mailagay sa loob ng VLOOKUP. Ang paglalagay ng isang function ay kinabibilangan ng pagpasok sa pangalawang function bilang isa sa mga argumento para sa unang function.
Ilagay ang Tutorial Data
Sa tutorial na ito, ang COLUMN function ay ipinasok bilang ang column index number argument para sa VLOOKUP. Ang huling hakbang sa tutorial ay nagsasangkot ng pagkopya ng lookup formula sa mga karagdagang column upang makuha ang mga karagdagang value para sa napiling bahagi.
Ang unang hakbang sa tutorial na ito ay ang pagpasok ng data sa isang Excel worksheet. Upang masundan ang mga hakbang sa tutorial na ito, ilagay ang data na ipinapakita sa larawan sa ibaba sa mga sumusunod na cell:
- Ilagay ang nangungunang hanay ng data sa mga cell D1 hanggang G1.
- Ilagay ang pangalawang hanay sa mga cell D4 hanggang G10.
Ang pamantayan sa paghahanap at ang lookup formula na ginawa sa tutorial na ito ay inilagay sa row 2 ng worksheet.
Hindi kasama sa tutorial na ito ang pangunahing Excel formatting na ipinapakita sa larawan, ngunit hindi ito nakakaapekto sa kung paano gumagana ang lookup formula.
Gumawa ng Pinangalanang Saklaw para sa Data Table
Ang pinangalanang hanay ay isang madaling paraan upang sumangguni sa isang hanay ng data sa isang formula. Sa halip na i-type ang mga cell reference para sa data, i-type ang pangalan ng range.
Ang pangalawang bentahe ng paggamit ng pinangalanang hanay ay ang mga cell reference para sa hanay na ito ay hindi kailanman nagbabago kahit na ang formula ay kinopya sa ibang mga cell sa worksheet. Ang mga pangalan ng range ay isang alternatibo sa paggamit ng mga absolute cell reference para maiwasan ang mga error kapag kumukopya ng mga formula.
Hindi kasama sa pangalan ng range ang mga heading o pangalan ng field para sa data (tulad ng ipinapakita sa row 4), ang data lang.
-
I-highlight ang cells D5 hanggang G10 sa worksheet.
Image -
Ilagay ang cursor sa Name Box sa itaas ng column A, i-type ang Table, pagkatapos ay pindutin ang Enter. Ang mga cell D5 hanggang G10 ay may pangalan ng hanay ng Talahanayan.
Image - Ang pangalan ng range para sa VLOOKUP table array argument ay ginagamit sa ibang pagkakataon sa tutorial na ito.
Buksan ang VLOOKUP Dialog Box
Bagama't posibleng direktang i-type ang lookup formula sa isang cell sa isang worksheet, maraming tao ang nahihirapang panatilihing tuwid ang syntax - lalo na para sa isang kumplikadong formula gaya ng ginamit sa tutorial na ito.
Bilang alternatibo, gamitin ang dialog box ng VLOOKUP Function Arguments. Halos lahat ng mga function ng Excel ay may dialog box kung saan ang bawat argumento ng function ay inilalagay sa isang hiwalay na linya.
-
Piliin ang cell E2 ng worksheet. Ito ang lokasyon kung saan ipapakita ang mga resulta ng two-dimensional lookup formula.
Image -
Sa ribbon, pumunta sa tab na Formulas at piliin ang Lookup & Reference.
Image -
Piliin ang VLOOKUP para buksan ang Function Arguments dialog box.
Image - Ang dialog box ng Function Arguments ay kung saan inilalagay ang mga parameter ng VLOOKUP function.
Ilagay ang Lookup Value Argument
Karaniwan, ang lookup value ay tumutugma sa isang field ng data sa unang column ng data table. Sa halimbawang ito, ang lookup value ay tumutukoy sa pangalan ng bahaging gusto mong mahanap ang impormasyon. Ang mga pinapayagang uri ng data para sa lookup value ay text data, logical value, numero, at cell reference.
Mga Ganap na Sanggunian sa Cell
Kapag ang mga formula ay kinopya sa Excel, nagbabago ang mga cell reference upang ipakita ang bagong lokasyon. Kung mangyari ito, ang D2, ang cell reference para sa lookup value, ay nagbabago at gumagawa ng mga error sa mga cell F2 at G2.
Hindi nagbabago ang mga absolute cell reference kapag kinopya ang mga formula.
Para maiwasan ang mga error, i-convert ang cell reference D2 sa isang absolute cell reference. Para gumawa ng absolute cell reference, pindutin ang F4 key. Nagdaragdag ito ng mga dollar sign sa paligid ng cell reference gaya ng $D$2.
-
Sa dialog box ng Function Arguments, ilagay ang cursor sa lookup_value text box. Pagkatapos, sa worksheet, piliin ang cell D2 upang idagdag ang cell reference na ito sa lookup_value. Ang cell D2 ay kung saan ilalagay ang pangalan ng bahagi.
Image -
Nang hindi ginagalaw ang insertion point, pindutin ang F4 key upang i-convert ang D2 sa absolute cell reference na $D$2.
Image - Iwanang bukas ang dialog box ng VLOOKUP function para sa susunod na hakbang sa tutorial.
Ipasok ang Argumento ng Table Array
Ang table array ay ang talahanayan ng data na hinahanap ng lookup formula upang mahanap ang impormasyong gusto mo. Ang array ng talahanayan ay dapat maglaman ng hindi bababa sa dalawang column ng data.
Ang unang column ay naglalaman ng lookup value argument (na na-set up sa nakaraang seksyon), habang ang pangalawang column ay hinahanap ng lookup formula upang mahanap ang impormasyong iyong tinukoy.
Dapat na ilagay ang argumento ng table array bilang isang range na naglalaman ng mga cell reference para sa data table o bilang isang pangalan ng range.
Para idagdag ang table ng data sa VLOOKUP function, ilagay ang cursor sa table_array text box sa dialog box at i-type ang Tableupang ipasok ang pangalan ng hanay para sa argumentong ito.
Nest the COLUMN Function
Karaniwan, ang VLOOKUP ay nagbabalik lamang ng data mula sa isang column ng isang talahanayan ng data. Ang column na ito ay itinakda ng column index number argument. Sa halimbawang ito, gayunpaman, mayroong tatlong column, at kailangang baguhin ang index number ng column nang hindi ine-edit ang lookup formula. Para magawa ito, ilagay ang COLUMN function sa loob ng VLOOKUP function bilang Col_index_num argument.
Kapag nesting function, hindi binubuksan ng Excel ang dialog box ng pangalawang function para ilagay ang mga argumento nito. Ang COLUMN function ay dapat na manu-manong ipasok. Ang COLUMN function ay mayroon lamang isang argument, ang Reference argument, na isang cell reference.
Ibinabalik ng COLUMN function ang numero ng column na ibinigay bilang Reference argument. Kino-convert nito ang column letter sa isang numero.
Upang mahanap ang presyo ng isang item, gamitin ang data sa column 2 ng talahanayan ng data. Ang halimbawang ito ay gumagamit ng column B bilang Reference para maglagay ng 2 sa Col_index_num argument.
-
Sa Function Arguments dialog box, ilagay ang cursor sa Col_index_num text box at i-type ang COLUMN(. (Siguraduhing isama ang open round bracket.)
Image -
Sa worksheet, piliin ang cell B1 para ilagay ang cell reference na iyon bilang Reference argument.
Image - Mag-type ng closing round bracket para makumpleto ang COLUMN function.
Ilagay ang VLOOKUP Range Lookup Argument
Ang Range_lookup argument ng VLOOKUP ay isang lohikal na value (TRUE o FALSE) na nagsasaad kung dapat maghanap ang VLOOKUP ng eksaktong o tinatayang tugma sa Lookup_value.
- TRUE o Inalis: Nagbabalik ang VLOOKUP ng malapit na tugma sa Lookup_value. Kung walang mahanap na eksaktong tugma, ibabalik ng VLOOKUP ang susunod na pinakamalaking halaga. Ang data sa unang column ng Table_array ay dapat ayusin sa pataas na pagkakasunud-sunod.
- FALSE: Gumagamit ang VLOOKUP ng eksaktong tugma sa Lookup_value. Kung mayroong dalawa o higit pang value sa unang column ng Table_array na tumutugma sa lookup value, gagamitin ang unang value na natagpuan. Kung hindi mahanap ang eksaktong tugma, isang N/A error ang ibabalik.
Sa tutorial na ito, hahanapin ang partikular na impormasyon tungkol sa isang partikular na hardware item, kaya ang Range_lookup ay nakatakda sa FALSE.
Sa dialog box ng Function Arguments, ilagay ang cursor sa Range_lookup text box at i-type ang False para sabihin sa VLOOKUP na magbalik ng eksaktong tugma para sa data.
Piliin ang OK para kumpletuhin ang lookup formula at isara ang dialog box. Maglalaman ang cell E2 ng N/A error dahil hindi pa naipasok ang pamantayan sa paghahanap sa cell D2. Ang error na ito ay pansamantala. Itatama ito kapag idinagdag ang pamantayan sa paghahanap sa huling hakbang ng tutorial na ito.
Kopyahin ang Lookup Formula at Ipasok ang Pamantayan
Ang lookup formula ay kumukuha ng data mula sa maraming column ng data table nang sabay-sabay. Upang gawin ito, ang formula ng paghahanap ay dapat na nasa lahat ng mga field kung saan mo gustong impormasyon.
Upang makuha ang data mula sa column 2, 3, at 4 ng talahanayan ng data (ang presyo, numero ng bahagi, at pangalan ng supplier), maglagay ng bahagyang pangalan bilang Lookup_value.
Dahil ang data ay inilatag sa isang regular na pattern sa worksheet, kopyahin ang lookup formula sa cell E2 hanggang cells F2 at G2 Habang kinokopya ang formula, ina-update ng Excel ang relative cell reference sa COLUMN function (cell B1) upang ipakita ang bagong lokasyon ng formula. Hindi binabago ng Excel ang ganap na cell reference (gaya ng $D$2) at ang pinangalanang hanay (Talahanayan) habang kinokopya ang formula.
Mayroong higit sa isang paraan upang kopyahin ang data sa Excel, ngunit ang pinakamadaling paraan ay ang paggamit ng Fill Handle.
-
Piliin ang cell E2, kung saan matatagpuan ang lookup formula, upang gawin itong aktibong cell.
Image -
I-drag ang fill handle sa cell G2. Ipinapakita ng mga cell F2 at G2 ang N/A error na nasa cell E2.
Image -
Upang gamitin ang mga formula ng paghahanap upang kunin ang impormasyon mula sa talahanayan ng data, sa worksheet piliin ang cell D2, i-type ang Widget, at pindutin ang Enter.
Image Ang sumusunod na impormasyon ay ipinapakita sa mga cell E2 hanggang G2.
- E2: $14.76 - ang presyo ng isang widget
- F2: PN-98769 - ang numero ng bahagi para sa isang widget
- G2: Widgets Inc. - ang pangalan ng supplier para sa mga widget
-
Upang subukan ang VLOOKUP array formula, i-type ang pangalan ng iba pang bahagi sa cell D2 at obserbahan ang mga resulta sa mga cell E2 hanggang G2.
Image - Ang bawat cell na naglalaman ng lookup formula ay naglalaman ng ibang piraso ng data tungkol sa hardware item na iyong hinanap.
Ang VLOOKUP function na may mga nested function tulad ng COLUMN ay nagbibigay ng isang mahusay na paraan upang maghanap ng data sa loob ng isang table, gamit ang iba pang data bilang isang lookup reference.
