Pagkaplag sa Daghang Fields sa Data uban sa Excel VLOOKUP

Pinaagi sa paghiusa sa Excel nga VLOOKUP function sa COLUMN function mahimo kita maghimo og lookup formula nga nagtugot kanimo sa pagbalik sa daghang mga mithi gikan sa usa ka laray sa usa ka database o talaan sa datos.

Sa panig-ingnan nga gipakita sa hulagway sa ibabaw, ang pormula sa lookup dali nga ibalik ang tanan nga mga hiyas - sama sa presyo, numero bahin, ug supplier nga may kalabutan sa lainlaing piraso sa hardware.

01 sa 10

Pagbalik sa Multiple nga mga Prinsipyo nga may Paglabaw sa VLOOKUP

Ang pagsunod sa mga lakang nga gilista sa ubos nagmugna sa lookup nga pormula nga nakita sa hulagway sa ibabaw nga magbalik sa daghang mga mithi gikan sa usa ka rekord sa datos.

Ang pormula sa lookup nagkinahanglan nga ang COLUMN function magamit sa sulod sa VLOOKUP.

Ang pagpahiluna sa usa ka katuyoan naglakip sa pagsulod sa ikaduhang gamit ingon nga usa sa mga argumento alang sa unang pag-obra.

Sa kini nga pagtulun-an, ang COLUMN function ipasulod isip argumento sa numero sa indeks sa VLOOKUP.

Ang katapusan nga lakang sa tutorial naglakip sa pagkopya sa lookup formula ngadto sa dugang nga mga kolum aron makuha ang mga dugang nga mga bili alang sa gipili nga bahin.

Mga Sulod sa Pagtudlo

• Pagsulod sa Giya sa Giya
• Pagtukod og Named Range alang sa Data Table
• Pagsugod sa VLOOKUP Function
• Pagsulod sa Value Value nga argumento gamit ang Absolute Cell References
• Pagsulod sa Tabel Array Argument
• Pagsulod sa Nested COLUMN Function
• Pagkompleto sa VLOOKUP Function
• Gikopirma ang Formula sa Pagtan-aw gamit ang Puno nga Puno
• Pagkuha og datos sa Lookup Formula

02 sa 10

Pagsulod sa Giya sa Pagtudlo

Ang unang lakang sa tutorial mao ang pagsulod sa datos ngadto sa usa ka worksheet sa Excel.

Aron masunod ang mga lakang sa panudlo ipasulod ang datos nga gipakita sa hulagway sa ibabaw ngadto sa mosunod nga mga selula .

• Pagsulod sa ibabaw nga kutay sa datos ngadto sa mga selula D1 ngadto sa G1
• Pagsulod sa ikaduhang hugna ngadto sa mga cell D4 ngadto sa G10

Ang search criteria ug ang lookup nga pormula nga gihimo sa panahon niini nga tutorial ipasulod ngadto sa row 2 sa worksheet.

Ang pagtudlo wala maglakip sa pag-format nga nakita sa hulagway, apan kini dili makaapekto kung giunsa ang mga pormula sa lookup nga magamit.

Ang impormasyon sa mga kapilian sa pag-format susama sa mga nakita sa ibabaw anaa sa kini nga Basic Excel Formatting Tutorial .

Mga Lakang sa Tutorial

1. Pagsulod sa datos nga makita sa hulagway sa itaas ngadto sa mga cell D1 ngadto sa G10

03 sa 10

Pagtukod og Named Range alang sa Data Table

Ang usa ka ginganlan nga gidak-on mao ang usa ka sayon ​​nga paagi sa pagtumong sa usa ka lainlaing mga datos sa pormula. Imbis nga mag-type sa mga reference sa cell alang sa datos, mahimo nimo nga ma-type ang ngalan sa range.

Ang ikaduha nga bentaha sa paggamit sa ginganlan nga gidak-on mao nga ang mga pakisayran sa cell alang niini nga gidak-on dili mausab bisan pa kon ang pormula gikopya sa ubang mga selula sa worksheet.

Busa ang mga pangalan sa kadak-on usa ka alternatibo sa paggamit sa hingpit nga mga reference sa cell aron mapugngan ang mga kasaypanan sa pagkopya sa mga pormula.

Mubo nga sulat: Ang pangalan sa range wala maglakip sa mga ulohan o mga ngalan sa field alang sa datos (laray 4) apan ang datos mismo.

Mga Lakang sa Tutorial

1. Ipasiugda ang mga cell D5 ngadto sa G10 sa worksheet aron mapili kini
2. I-klik ang Name Box nga nahimutang sa taas nga kolum A
3. Isulat ang "Table" (walay mga kinutlo) sa Ngalan sa Ngalan
4. Ipadayon ang ENTER key sa keyboard
5. Ang mga cell D5 hangtod sa G10 karon adunay ngalan sa "Table". Atong gamiton ang ngalan alang sa VLOOKUP table array argument sa ulahi sa tutorial

04 sa 10

Pag-abli sa VLOOKUP Dialog Box

Bisan tuod posible nga i-type lang ang pormula sa lookup diretso sa usa ka cell sa usa ka worksheet, daghang mga tawo ang nalisdan sa pagpabilin nga matul-id ang syntax - ilabi na sa usa ka komplikadong porma sama sa usa nga atong gigamit niini nga panudlo.

Usa ka alternatibo, sa kini nga kaso, mao ang paggamit sa VLOOKUP nga kahon sa dialogo . Hapit ang tanan nga mga paglihok sa Excel adunay usa ka kahon sa dialogo nga nagtugot kanimo sa pagsulod sa matag usa sa mga argumento sa function sa usa ka linain nga linya.

Mga Lakang sa Tutorial

1. I-klik ang cell E2 sa worksheet - ang nahimutangan diin ang mga resulta sa duha ka yugto nga lookup formula makita
2. I-klik ang tabula sa Formula sa ribbon
3. I-klik ang opsiyon sa Lookup & Reference sa ribbon aron maablihan ang lista sa function nga drop down
4. I-klik ang VLOOKUP sa listahan aron maablihan ang kahon sa dialogo sa function

05 sa 10

Pagsulod sa Value Value nga argumento gamit ang Absolute Cell References

Kasagaran, ang bili sa pagpangita adunay usa ka laraw sa datos sa unang kolum sa talaan sa datos.

Sa atong panig-ingnan, ang bili sa pagpangita nagtumong sa ngalan sa bahin sa hardware nga gusto nato pangitaon ang impormasyon.

Ang gitugot nga matang sa datos alang sa lookup value mao ang:

• datos sa teksto
• usa ka lohikal nga bili (TINUOD o bakak lamang)
• usa ka numero
• usa ka cell nga nagtumong sa usa ka bili sa worksheet

Sa niini nga panig-ingnan, kita mosulod sa cell reference kon asa nahimutang ang bahin nga ngalan - cell D2.

Mga Absolute References sa Cell

Sa ulahing lakang sa tutorial, atong kopyahon ang lookup formula sa cell E2 ngadto sa mga cell F2 ug G2.

Kasagaran, sa dihang ang mga pormula gikopya sa Excel, ang mga reference sa cell mag-usab aron mapakita ang ilang bag-ong lokasyon.

Kung kini mahitabo, ang D2 - ang cell reference alang sa lookup value - mausab samtang ang pormula gikopya nga nagmugna og mga sayup sa mga cell F2 ug G2.

Aron mapugngan ang mga kasaypanan, atong i-convert ang cell reference D2 ngadto sa hingpit nga reference sa cell .

Ang hingpit nga mga pakisayran sa cell dili mausab kung ang mga pormula gikopya.

Ang hingpit nga mga pakisayran sa selula gihimo pinaagi sa pagpugos sa F4 key sa keyboard. Ang paghimo sa ingon nagdugang dolyar nga mga timaan sa palibot sa cell reference sama sa $ D $ 2

Mga Lakang sa Tutorial

1. I-klik ang lookup_value line sa dialog box
2. I-klik ang cell D2 aron idugang kini nga cell reference sa lookup_value line. Kini mao ang selula diin atong pag-type ang parte nga ngalan diin kita nangita og kasayuran
3. Kung walay paglihok sa insertion point, i-press ang F4 key sa keyboard aron ma-convert ang D2 ngadto sa absolute reference sa cell $ D $ 2
4. Biyai ang kahon sa dialogo sa VLOOKUP function nga bukas alang sa sunod nga lakang sa tutorial

06 sa 10

Pagsulod sa Tabel Array Argument

Ang han-ay sa lamesa mao ang lamesa sa datos nga gipangita sa lookup formula aron makit-an ang impormasyon nga gusto nato.

Ang hanay sa lamesa kinahanglan nga adunay labing menos duha ka kolum sa datos .

• ang unang kolum naglangkob sa lookup value argument (sa miaging lakang sa tutorial)
• ang ikaduha, ug bisan unsang dugang nga mga kolum, susihon sa pormula sa lookup aron sa pagpangita sa kasayuran nga atong gipiho.

Ang argumento sa lamesang array kinahanglan nga gisulod ingon nga usa ka hanay nga adunay sulod nga mga reference sa cell alang sa talaan sa datos o ingon nga usa ka range name .

Alang niini nga pananglitan, atong gamiton ang range name nga gimugna sa step 3 sa tutorial.

Mga Lakang sa Tutorial

1. I-klik ang linya sa table_array sa dialog box
2. Isulat ang "Table" (walay mga kinutlo) aron mosulod sa range name alang niini nga argumento
3. Biyai ang kahon sa dialogo sa VLOOKUP function nga bukas alang sa sunod nga lakang sa tutorial

07 sa 10

Nagpahiluna sa COLUMN Function

Kasagaran ang VLOOKUP nagbalik lamang sa mga datos gikan sa usa ka kolum sa usa ka talaan sa datos ug kini nga kolum gipahimutang sa column argument number number .

Sa niini nga panig-ingnan, bisan pa niana, kita adunay tulo ka mga kolum nga gusto natong ibalik ang mga datos gikan sa mao nga kita nagkinahanglan sa usa ka paagi sa dali nga pag-usab sa numero sa numero sa kolum nga wala mag-edit sa atong lookup formula.

Dinhi diin ang function sa COLUMN moabut. Pinaagi sa pagsulod niini ingon nga argumento sa numero sa index sa column , kini mausab samtang ang formula sa lookup gikopya gikan sa cell D2 ngadto sa mga cell E2 ug F2 sa ulahi sa tutorial.

Nananom nga mga Katungdanan

Busa, ang COLUMN function naglihok isip argumento sa numero sa indeks sa VLOOKUP.

Gihimo kini pinaagi sa paghimo sa COLUMN function sulod sa VLOOKUP sa linya sa Col_index_num sa dialog box.

Pagsulod sa COLUMN Function Manwal

Sa diha nga ang pag-andam sa paglihok, ang Excel dili magtugot kanato sa pag-abli sa kahon sa dialogo sa ikaduha nga pag-obra aron mosulod sa mga argumento niini.

Busa, ang COLUMN function kinahanglan nga gisudlan nga manu-mano sa linya sa Col_index_num .

Ang COLUMN function adunay usa lamang nga argumento - ang Reference argument nga usa ka reference sa cell.

Pagpili sa COLUMN Function's Reference Argument

Ang trabaho sa COLUMN function mao ang pagbalik sa gidaghanon sa kolum nga gihatag isip Reference argument.

Sa laing pagkasulti, kini nag-usab sa kolum nga kolum ngadto sa usa ka numero nga may kolum nga A nga mao ang unang kolum, kolum B ang ikaduha ug uban pa.

Sukad ang unang natad sa datos nga gusto natong ibalik mao ang bili sa butang - nga anaa sa kolum nga duha sa data table - mahimo natong pilion ang cell reference alang sa bisan unsang selula sa kolum B isip Reference Argument aron makuha ang numero 2 alang sa ang argumento sa Col_index_num .

Mga Lakang sa Tutorial

1. Sa kahon sa dialogo sa VLOOKUP function, i-klik ang linya sa Col_index_num
2. I-type ang kolum sa pangalan sa function nga gisundan sa usa ka bukas nga round bracket " ( "
3. I-klik ang cell B1 sa worksheet aron pagsulod sa reference sa cell nga Reference reference
4. I-type ang usa ka panapos nga hugna nga bracket " ) " aron makompleto ang function sa COLUMN
5. Biyai ang kahon sa dialogo sa VLOOKUP function nga bukas alang sa sunod nga lakang sa tutorial

08 sa 10

Pagsulod sa VLOOKUP Range Lookup Argument

Ang argumento sa Range_lookup sa VLOOKUP usa ka lohikal nga bili (TRUE o FALSE lamang) nga nagpakita kon gusto nimo ang VLOOKUP aron makit-an ang eksaktong o may gibanabanang pagkapareha sa Lookup_value.

• Kon TINUOD o kung wala kini nga argumento , ang VLOOKUP nagbalik bisan asa nga eksaktong tugma sa Lookup_value, o, kung wala ang eksaktong tugma, ang VLOOKUP nagbalik sa sunod nga kinadak-ang bili. Alang sa pormula sa paghimo niini, ang datos sa unang kolum sa Table_array kinahanglan ibutang sa sunod nga pagkasunod .
• Kung FALSE, ang VLOOKUP mogamit lamang sa eksaktong tugma sa Lookup_value. Kon adunay duha o labaw pa nga mga hiyas sa unang kolum sa Table_array nga mohaum sa lookup value, ang unang bili nga nakit-an gigamit. Kung wala ang eksaktong tugma, usa ka # N / A nga sayop ang ibalik.

Niini nga pagtulun-an, tungod kay kita nangita alang sa piho nga kasayuran mahitungod sa usa ka butang nga hardware, kita magtakda sa Range_lookup nga sama sa Bakak .

Mga Lakang sa Tutorial

1. I-klik ang linya sa Range_lookup sa dialog box
2. Isulat ang pulong nga Bakak sa kini nga linya aron ipakita nga gusto nato ang VLOOKUP nga ibalik ang eksakto nga tugma alang sa datos nga atong gipangita
3. I-klik ang OK aron makompleto ang lookup formula ug suod nga dialog box
4. Tungod kay wala pa kami makasulod sa criteria sa lookup ngadto sa cell D2 ang usa ka # N / A error nga anaa sa cell E2
5. Kini nga sayop pagatul-id kon atong dugangan ang mga criteria sa lookup sa katapusang lakang sa tutorial

09 sa 10

Gikopirma ang Formula sa Pagtan-aw gamit ang Puno nga Puno

Ang lookup formula gituyo aron makuha ang data gikan sa daghang mga kolum sa data table sa usa ka higayon.

Aron mahimo kini, ang pormula sa lookup kinahanglan magpuyo sa tanan nga natad diin kita gusto nga impormasyon.

Sa kini nga pagtulun-an gusto namo nga makuha ang datos gikan sa mga kolum 2, 3, ug 4 sa data table - nga mao ang bili, numero sa parte, ug ang ngalan sa supplier kung kita mosulod sa usa ka bahin nga ngalan sama sa Lookup_value.

Tungod kay ang datos gibutang sa usa ka regular nga sumbanan sa worksheet , mahimo natong kopyahon ang lookup formula sa cell E2 ngadto sa mga cell F2 ug G2.

Ingon nga gikopya ang pormula, ang Excel mag-update sa relatibo nga reference sa cell sa COLUMN function (B1) aron mapakita ang bag-ong lokasyon sa pormula.

Ingon man, ang excel wala mag-usab sa hingpit nga reference sa cell $ D $ 2 ug ang ginganlan nga Talaan sa numero nga gikopya.

Adunay labaw pa sa usa ka paagi sa pagkopya sa datos sa Excel, apan tingali ang labing sayon ​​nga paagi mao ang paggamit sa Fill Handle .

Mga Lakang sa Tutorial

1. I-klik ang cell E2 - diin nahimutang ang lookup formula - aron mahimo kini nga aktibo nga cell
2. Ibutang ang pointer sa mouse ibabaw sa itom nga kwadro sa ubos sa tuo nga eskina. Ang pointer mag-usab ngadto sa usa ka dugang nga karatula " + " - kini mao ang puno nga gunitanan
3. I-klik ang wala nga button sa mouse ug i-drag ang hawanan sa puno ngadto sa cell G2
4. Gipagawas ang button sa mouse ug ang cell F3 kinahanglan nga adunay duha ka yugto nga look-up formula
5. Kon himoon ang husto, ang mga cell F2 ug G2 kinahanglan na usab nga adunay # N / A error nga anaa sa cell E2

10 sa 10

Pagsulod sa Lookup Criteria

Sa higayon nga ang pormula sa lookup gikopya ngadto sa gikinahanglan nga mga selula kini magamit aron makuha ang kasayuran gikan sa talaan sa datos.

Sa pagbuhat sa ingon, i-type ang ngalan sa butang nga gusto nimo makuha sa Lookup_value cell (D2) ug i-press ang ENTER key sa keyboard.

Kung nahuman na, ang matag cell nga naglangkob sa lookup formula kinahanglan nga adunay lain nga piraso sa datos mahitungod sa hardware item nga imong gipangita.

Mga Lakang sa Tutorial

1. I-klik ang cell D2 sa worksheet
2. Ibutang ang Widget sa cell D2 ug i-press ang ENTER key sa keyboard
3. Ang mosunod nga impormasyon kinahanglan ipakita sa mga cell E2 ngadto sa G2:
   • E2 - $ 14.76 - ang presyo sa usa ka widget
   • F2 - PN-98769 - ang bahin nga numero alang sa usa ka widget
   • G2 - Widgets Inc. - ang ngalan sa supplier alang sa mga widgets
4. Sulayi ang dagway sa formula sa VLOOKUP pinaagi sa pag-type sa ngalan sa ubang mga bahin ngadto sa cell D2 ug pagpaniid sa mga resulta sa mga selula E2 ngadto sa G2

Kung ang usa ka mensahe sa sayop sama sa #REF! makita sa mga selula nga E2, F2, o G2, kining listahan sa mga VLOOKUP nga mga mensahe sa sayup mahimong makatabang kanimo sa pagtino kon asa nahimutang ang problema.