Theory
બીજી sheet માંથી price લાવો
Aryan પાસે એક sheet પર એક price list છે (item names અને prices), અને એક sales sheet જે વેચાયેલા items યાદી કરે છે. એને દરેક sale ની price price list માંથી આપોઆપ ખેંચાવવી છે, item name થી match કરતા, 5000 rows માટે.
એમને type કરવા અશક્ય છે. હાથે copy કરવું ભૂલ-પ્રવણ છે. આ 'બીજા table માં એક value ને એની key થી જોવી' business Excel માં એકલો સૌથી ઉપયોગી function છે: VLOOKUP.
એ સૌથી કુખ્યાત ફાંદા વાળો પણ છે: એક નાનકડો ચોથો argument જે, ખોટો છોડ્યો, પ્રશંસનીય પણ ખોટો data છાનેમાને પાછો આપે છે. આમાં નિપુણતા અને તમે elective માં નિપુણ.
Theory
એક directory માં એક name જોવો
એક phone directory name થી sorted છે; તમે name શોધો છો, પછી number સુધી આરપાર વાંચો છો. VLOOKUP બરાબર આ કરે છે: તમે એને એક key આપો છો (item name), એ એ key ને એક table ના પહેલા column માં શોધે છે, પછી તમારા ઇચ્છિત column સુધી આરપાર વાંચે છે (price). પેચ: એક directory lookup ફક્ત ત્યારે કામ કરે છે જ્યારે તમે name બરાબર-બરાબર match કરો, 'Riya Shah' ' Riya Shah' નથી. VLOOKUP નો exact-match switch એ જ માંગ છે.
Theory
VLOOKUP ની શારીરિક રચના
સામાન્ય રૂપ, પછી એક અસલી ઉદાહરણ:
=VLOOKUP( lookup_value , table_array , col_index , [exact?] )
=VLOOKUP( A2 , PriceList!$A$2:$B$100 , 2 , FALSE )
ઉદાહરણ વાંચવું: A2 (item name) ને locked price-list table $A$2:$B$100 માં શોધો, જેની key column 1 માં બેસે છે; બીજું column પાછું આપો (price); અને FALSE એક exact match માંગે છે. એ છેલ્લો argument ક્યારેય ન છોડો.
Theory
ચાર arguments, અને ઘાતક છેલ્લો વાળો
- lookup_value: શું શોધવું (A2 માં item name).
- table_array: શોધવાનું table; key એનું પહેલું column હોવું જોઈએ. એને
$થી lock કરો જેથી એ ખસે નહીં. - col_index_num: કયું column પાછું આપવું, table ની ડાબી બાજુ થી ગણેલું (price column 2 છે).
- range_lookup: FALSE = exact match (જે તમે ઇચ્છો), TRUE = approximate (પહેલું column ascending sorted જોઈએ; grade bands માટે વપરાય).
ફાંદો: ચોથો argument છોડો અને એ TRUE પર default થાય છે, એક approximate match કરતા જે ખોટા item ની price કોઈ error વગર પાછી આપી શકે.
Quiz
Aryan =VLOOKUP(A2, PriceList, 2) લખે છે અને FALSE ભૂલી જાય છે. એની item list unsorted છે. જોખમ શું છે?
- એ એક APPROXIMATE match કરે છે અને છાનેમાને ખોટી price પાછી આપી શકે છે
- એ દરેક row માટે #N/A બતાવે છે
- એ બિલકુલ ઠીક છે, FALSE default છે
- એ price ના બદલે item name પાછું આપે છે
Show the answer
એ એક APPROXIMATE match કરે છે અને છાનેમાને ખોટી price પાછી આપી શકે છે
ચોથો argument છોડવો એને TRUE (approximate match) પર default કરે છે, જે માને છે પહેલું column ascending sorted છે. unsorted data પર એ જેની પાસે ઉતરે એ પાછું આપે છે, એક ખોટી price, તમને ચેતવવા કોઈ error નહીં. આ છાનેમાને-ખોટો-જવાબ સૌથી ખતરનાક VLOOKUP bug છે. નિયમ: exact matching માટે હંમેશા VLOOKUP ને FALSE થી પૂરું કરો.
Think first
VLOOKUP ડાબી બાજુ ન જોઈ શકે
Aryan ના table માં price column A માં અને item name column B માં છે. એ એક item name શોધીને એની price (ડાબી બાજુ) પાછી આપવા માંગે છે. VLOOKUP અહીં કેમ નિષ્ફળ થાય છે, અને શું એને ઉકેલે છે?
Show the answer
VLOOKUP ફક્ત પહેલું column શોધી શકે છે અને એની જમણી બાજુ કંઈક પાછું આપી શકે છે, એ ડાબી બાજુ ન જોઈ શકે. name column B માં અને price column A માં હોવાથી, price key ની ડાબી બાજુ છે, તો VLOOKUP એને લાવી ન શકે. ઉકેલ: columns ફરી ગોઠવો (key પહેલા), INDEX/MATCH વાપરો, કે સૌથી સારું, XLOOKUP, જે કોઈ પણ દિશા માં જુએ છે, default રીતે બરાબર match કરે છે, અને કોઈ column-index ગણતરી નથી માંગતું. આ left-lookup મર્યાદા બરાબર કારણ છે કે XLOOKUP બન્યું.
Watch out
Marks ક્યાં કપાય છે
મોટો વાળો: FALSE છોડવો (exact match), છાનેમાને ખોટા જવાબ આપતા. એ ભૂલવું કે key table નું પહેલું column હોવું જોઈએ, અને VLOOKUP ડાબી બાજુ ન જોઈ શકે. table_array ને $-lock ન કરવો (copy થાય ત્યારે ખસે છે, Unit 2 bug). col_index ખોટું ગણવું (table ની ડાબી બાજુ થી, અને એક column નાખો તો તૂટે છે). અને #N/A એટલે ન મળ્યું, IFERROR/IFNA થી લપેટો. HLOOKUP horizontal જોડિયો છે; XLOOKUP આધુનિક fix છે. આ topic elective નો marks નો સૌથી સમૃદ્ધ સ્રોત છે.
Theory
સૌથી મૂલ્યવાન Excel કૌશલ્ય જે તમે શીખશો
VLOOKUP (અને XLOOKUP) એ છે જે employers નો અર્થ હોય છે જ્યારે એક job ad કહે છે 'Excel આવડવું જોઈએ'. એ tables ને જોડે છે, બરાબર તમારા BCA105 database unit વાળો relational-join idea, એક spreadsheet માં કરેલો. એને TRIM (પહેલા key સાફ કરો) અને IFERROR (#N/A સંભાળો) સાથે મેળવો bulletproof lookups માટે. આગળનો lesson: date અને time functions ફરી જોયેલા, age અને tenure માટે DATEDIF, વિશ્લેષણ power tools પહેલા.
Summary
Key takeaways
- VLOOKUP(lookup_value, table_array, col_index, [range_lookup]) એક key match કરીને બીજા table માંથી એક value લાવે છે.
- એ PAHELU column શોધે છે અને એની JAMNI બાજુ એક column પાછું આપે છે; એ ડાબી બાજુ ન જોઈ શકે.
- ચોથા argument તરીકે હંમેશા FALSE (exact match) વાપરો; TRUE (approximate) sorted data જોઈએ અને ખોટા પરિણામ પાછા આપી શકે.
- table_array ને $ થી lock કરો; #N/A એટલે ન મળ્યું (IFERROR/IFNA થી લપેટો).
- HLOOKUP horizontal સંસ્કરણ છે; XLOOKUP લવચીક આધુનિક replacement છે (કોઈ પણ દિશા, default રીતે exact).
- યાદ રાખવાની યુક્તિ: directory ના પહેલા column માં name શોધો, આરપાર વાંચો; BARABAR match કરો.