Excel में VLOOKUP और XLOOKUP Basics
Marks, fees, roll numbers या employee records table से automatically find करें.
What lookup functions do
A lookup finds one value in a table and brings back related information – like typing a roll number and getting the student’s name and marks automatically. It saves hours when you prepare result sheets, fee reports or price lists.
We use this sheet (named Data) in all examples:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Roll | Name | Class | Marks |
| 2 | 101 | Asha | 10-A | 91 |
| 3 | 102 | Ravi | 10-A | 58 |
| 4 | 103 | Neha | 10-B | 76 |
| 5 | 104 | Kabir | 10-B | 34 |
| 6 | 105 | Isha | 10-A | 88 |
VLOOKUP step by step
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
| Part | Meaning | In our example |
|---|---|---|
| lookup_value | What you search for | 103 (or a cell like G2) |
| table_array | The table; the search column must be the first column | $A$2:$D$6 |
| col_index_num | Which column to return, counting from the first column of the table | 2 = Name, 4 = Marks |
| range_lookup | FALSE = exact match (use this almost always) | FALSE |
| Formula | Result |
|---|---|
=VLOOKUP(103, $A$2:$D$6, 2, FALSE) | Neha |
=VLOOKUP(103, $A$2:$D$6, 4, FALSE) | 76 |
=VLOOKUP(110, $A$2:$D$6, 2, FALSE) | #N/A (roll 110 does not exist) |
=IFERROR(VLOOKUP(110, $A$2:$D$6, 2, FALSE), "Not found") | Not found |
VLOOKUP’s three limits
- It can only look to the right: you cannot find a roll number from a name with VLOOKUP on this table.
- Column numbers break when someone inserts a column inside the table (4 now points to the wrong column).
- Forgetting FALSE gives an approximate match – a wrong name silently, if the first column is not sorted.
XLOOKUP: the modern, safer way
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found]) – available in Excel 2021, Microsoft 365, Excel for the web, Google Sheets and recent LibreOffice Calc (24.8+).
| Formula | Result |
|---|---|
=XLOOKUP(103, A2:A6, B2:B6) | Neha |
=XLOOKUP("Kabir", B2:B6, A2:A6) | 104 (looks to the left – impossible with VLOOKUP) |
=XLOOKUP(110, A2:A6, B2:B6, "Not found") | Not found (no IFERROR needed) |
=XLOOKUP(103, A2:A6, B2:D6) | Neha | 10-B | 76 (spills into three cells) |
=INDEX(B2:B6, MATCH(103, A2:A6, 0)) – it also works to the left and survives inserted columns.Real example: grade from marks (approximate match done right)
Make a small grade table in G2:H6 with the lower limit of each grade, sorted ascending: 0 → E, 33 → D, 50 → C, 75 → B, 90 → A. Then:
| Formula (in E2, copied down) | Results for 91, 58, 76, 34, 88 |
|---|---|
=VLOOKUP(D2, $G$2:$H$6, 2, TRUE) | A, C, B, D, B |
=XLOOKUP(D2, $G$2:$G$6, $H$2:$H$6, , -1) | A, C, B, D, B (match mode -1 = exact or next smaller) |
This is the only common case where approximate match (TRUE) is correct – and only because the limits are sorted.
Fixing #N/A and other errors
| Problem | Cause | Fix |
|---|---|---|
| #N/A for a value that is visible | Extra spaces (“Neha ”) or number stored as text (“103”) | =TRIM() the names; convert text to numbers with Data → Text to Columns or =VALUE() |
| #REF! | col_index_num is larger than the table width | Count columns again or switch to XLOOKUP |
| Wrong result, no error | range_lookup left blank (TRUE) | Add FALSE |
| Results change after copying down | Missing $ in the table range | Use $A$2:$D$6 |
Practice
- Type the Data table above and make a “Search” box: G1 = Roll, H1 = formula for Name, I1 = Marks.
- Use XLOOKUP to find the roll number of “Isha”.
- Add the grade table and fill grades for all students.
- Make a second sheet “Fees” with Roll and Fee paid, then bring the fee into the Data sheet with XLOOKUP and show “Pending” when it is missing.
More formulas for result work: basic Excel formulas for school results.
Lookup functions क्या करते हैं
Lookup किसी table में एक value ढूँढकर उससे जुड़ी जानकारी लाता है – जैसे roll number लिखते ही student का नाम और marks अपने-आप आ जाएँ। Result sheets, fee reports या price lists बनाते समय इससे घंटों बचते हैं।
सभी उदाहरणों में यह sheet (नाम Data) इस्तेमाल होगी:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Roll | Name | Class | Marks |
| 2 | 101 | Asha | 10-A | 91 |
| 3 | 102 | Ravi | 10-A | 58 |
| 4 | 103 | Neha | 10-B | 76 |
| 5 | 104 | Kabir | 10-B | 34 |
| 6 | 105 | Isha | 10-A | 88 |
VLOOKUP step by step
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
| हिस्सा | अर्थ | हमारे उदाहरण में |
|---|---|---|
| lookup_value | जो ढूँढना है | 103 (या G2 जैसी cell) |
| table_array | Table; ढूँढने वाला column पहला होना चाहिए | $A$2:$D$6 |
| col_index_num | कौन सा column लौटाना है, table के पहले column से गिनकर | 2 = Name, 4 = Marks |
| range_lookup | FALSE = exact match (लगभग हमेशा यही) | FALSE |
| Formula | नतीजा |
|---|---|
=VLOOKUP(103, $A$2:$D$6, 2, FALSE) | Neha |
=VLOOKUP(103, $A$2:$D$6, 4, FALSE) | 76 |
=VLOOKUP(110, $A$2:$D$6, 2, FALSE) | #N/A (roll 110 नहीं है) |
=IFERROR(VLOOKUP(110, $A$2:$D$6, 2, FALSE), "Not found") | Not found |
VLOOKUP की तीन सीमाएँ
- यह सिर्फ़ दाईं ओर देख सकता है: इस table पर VLOOKUP से नाम से roll number नहीं ढूँढ सकते।
- Table के अंदर कोई column जोड़ दे तो column numbers ग़लत हो जाते हैं (4 अब ग़लत column दिखाता है)।
- FALSE भूलने पर approximate match – पहला column sorted न हो तो चुपचाप ग़लत नाम।
XLOOKUP: नया, ज़्यादा सुरक्षित तरीका
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found]) – Excel 2021, Microsoft 365, Excel for the web, Google Sheets और नए LibreOffice Calc (24.8+) में उपलब्ध।
| Formula | नतीजा |
|---|---|
=XLOOKUP(103, A2:A6, B2:B6) | Neha |
=XLOOKUP("Kabir", B2:B6, A2:A6) | 104 (बाईं ओर देखता है – VLOOKUP से असंभव) |
=XLOOKUP(110, A2:A6, B2:B6, "Not found") | Not found (IFERROR की ज़रूरत नहीं) |
=XLOOKUP(103, A2:A6, B2:D6) | Neha | 10-B | 76 (तीन cells में फैलता है) |
=INDEX(B2:B6, MATCH(103, A2:A6, 0)) इस्तेमाल करें – यह भी बाईं ओर काम करता है और जोड़े गए columns से नहीं टूटता।असली उदाहरण: marks से grade (approximate match सही तरीके से)
G2:H6 में हर grade की निचली सीमा के साथ छोटी grade table बनाएँ, बढ़ते क्रम में: 0 → E, 33 → D, 50 → C, 75 → B, 90 → A। फिर:
| Formula (E2 में, नीचे copy) | 91, 58, 76, 34, 88 के नतीजे |
|---|---|
=VLOOKUP(D2, $G$2:$H$6, 2, TRUE) | A, C, B, D, B |
=XLOOKUP(D2, $G$2:$G$6, $H$2:$H$6, , -1) | A, C, B, D, B (match mode -1 = exact या अगला छोटा) |
यही एक आम स्थिति है जहाँ approximate match (TRUE) सही है – और सिर्फ़ इसलिए कि सीमाएँ sorted हैं।
#N/A और दूसरे errors ठीक करना
| समस्या | कारण | सुधार |
|---|---|---|
| दिखती value के लिए #N/A | Extra spaces (“Neha ”) या number text के रूप में (“103”) | नामों पर =TRIM(); Data → Text to Columns या =VALUE() से text को number बनाएँ |
| #REF! | col_index_num table की चौड़ाई से बड़ा | Columns दोबारा गिनें या XLOOKUP लें |
| ग़लत नतीजा, कोई error नहीं | range_lookup ख़ाली (TRUE) | FALSE जोड़ें |
| नीचे copy करने पर नतीजे बदलें | Table range में $ नहीं | $A$2:$D$6 लिखें |
अभ्यास
- ऊपर की Data table type करके “Search” box बनाएँ: G1 = Roll, H1 = Name का formula, I1 = Marks।
- XLOOKUP से “Isha” का roll number ढूँढें।
- Grade table जोड़कर सभी students के grades भरें।
- दूसरी sheet “Fees” में Roll और Fee paid बनाएँ, फिर XLOOKUP से fee को Data sheet में लाएँ और न मिले तो “Pending” दिखाएँ।
Result के काम के और formulas: school result के लिए basic Excel formulas।
अक्सर पूछे जाने वाले सवाल
VLOOKUP और XLOOKUP में क्या अंतर है?
VLOOKUP सिर्फ़ पहले column में ढूँढता है और number से column लौटाता है, सिर्फ़ दाईं ओर देखते हुए। XLOOKUP में ढूँढने और लौटाने की ranges अलग होती हैं, बाईं ओर देख सकता है, built-in “not found” value है और default exact match है।
VLOOKUP #N/A क्यों देता है?
Value ठीक-ठीक नहीं मिली – अक्सर extra spaces, text के रूप में रखे numbers, या typo की वजह से। Data साफ़ करने के लिए TRIM/VALUE और दोस्ताना message के लिए IFERROR या XLOOKUP का if_not_found इस्तेमाल करें।
क्या Excel 2019 में XLOOKUP है?
नहीं। यह Excel 2021, Microsoft 365 और Excel for the web में है। Excel 2019 या पुराने में INDEX + MATCH इस्तेमाल करें।
