Complete Study Notes for MS Excel Lookup Formulas Quiz
Chahe aap corporate MIS executive job interview ki tayyari kar rahe hon, ADCA/DCA diploma student hon, ya SSC/Banking computer awareness exam de rahe hon, MS Excel Lookup Formulas Quiz solve karna aapki practical data-handling skills ko next level par le jata hai. Bade spreadsheets mein hazaaron rows ke beech se kisi specific employee ID, product price, ya student result ko turant dhoondhne ke liye Microsoft Excel ke Lookup & Reference functions sabse zyada istemal hote hain.
Interviews aur practical tests mein aksar candidates VLOOKUP ki limitations (jaise left-side lookup na kar paana) aur XLOOKUP ya INDEX-MATCH ke faydon mein atak jaate hain. Isliye neeche diye gaye 25 questions ko attempt karne se pehle in sabhi lookup functions ke syntax aur key rules ko ek baar revise kar lein:
1. VLOOKUP, HLOOKUP, XLOOKUP & INDEX-MATCH Comparison Table
| Function Name | Lookup Direction | Default Match Mode | Key Limitation / Advantage |
|---|---|---|---|
| VLOOKUP | Vertical (Top to Bottom) | Approximate Match (TRUE) |
Sirf Right side columns se value nikal sakta hai; Left lookup nahi karta. |
| HLOOKUP | Horizontal (Left to Right) | Approximate Match (TRUE) |
Top row mein value search karke neeche ki rows se data lata hai. |
| XLOOKUP | Vertical & Horizontal Both | Exact Match (0) |
Left aur Right dono taraf lookup karta hai; built-in if_not_found error handling deta hai. |
| INDEX + MATCH | Any Direction (2-Way Lookup) | Configurable (0 for Exact) |
Column insert/delete karne par formula break nahi hota; har Excel version mein chalta hai. |
2. Exam & Job Interview Mein Pooche Jaane Wale Important Rules
- VLOOKUP Syntax & Arguments:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). Ismein exact match ke liye 4th argument meinFALSEya0likhna zaroori hota hai. Agar lookup value table ke pehle column mein nahi milti, toh Excel#N/Aerror dikhata hai. - INDEX & MATCH Ka Combination:
MATCHfunction kisi range mein item ki relative position (row ya column number) batata hai, jabkiINDEXfunction diye gaye row aur column number par maujood actual value return karta hai. Dono ko milakar kisi bhi direction mein dynamic lookup kiya ja sakta hai. - XLOOKUP Ke Modern Features: Excel 365 aur Excel 2021 mein aaye
XLOOKUPfunction mein poore table ko select karne ki zaroorat nahi hoti—ismein sirflookup_arrayaurreturn_arrayselect karte hain, aur yeh by default Exact Match karta hai. - Common Lookup Errors: Jab
col_index_numselected table ke total columns se zyada ho jata hai toh#REF!error aata hai, aur jabcol_index_num1 se kam (0 ya negative) diya jata hai toh#VALUE!error aata hai.
Quiz Completed!
Frequently Asked Questions (FAQs) on Excel Lookup Formulas
Kyunki VLOOKUP sirf left-to-right data search kar sakta hai aur table mein beech mein naya column insert karne par iska static column index number galat result dene lagta hai, jabki INDEX-MATCH left-to-right aur right-to-left dono taraf kaam karta hai aur column insert karne par bhi break nahi hota.
Nahi, XLOOKUP sirf Microsoft 365, Excel 2021, aur Excel for Web mein available hai. Purane versions (Excel 2016/2019) mein XLOOKUP ki jagah INDEX-MATCH ya VLOOKUP ka use kiya jata hai.
Aap VLOOKUP formula ko IFERROR function ke andar wrap kar sakte hain—jaise =IFERROR(VLOOKUP(...), "Not Found")—jisse error ki jagah clean text dikhai dega.

