4.3 HLOOKUP
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]) – same as VLOOKUP but searches the first row and returns from a row below.
Worked example – monthly targets stored horizontally (sheet Targets, A1:E3):
| Aug | Sep | Oct | Nov | |
|---|---|---|---|---|
| Pune Target | ₹4,00,000 | ₹5,50,000 | ₹4,80,000 | ₹6,00,000 |
| Nashik Target | ₹1,50,000 | ₹1,90,000 | ₹1,60,000 | ₹2,20,000 |
About this data: Teaching sample for a fictional shop “BrightLedger Finance”. Figures and names (e.g. Neha Shah) are made up for this lesson on hlookup.
=HLOOKUP("Sep",Targets!$A$1:$E$3,2,FALSE) → ₹5,50,000 (Pune, Ganeshotsav month). Row 3 would give Nashik's target.
Ravindra Bagale's Tip
HLOOKUP is used less these days – many students think it is the only option for horizontal data. XLOOKUP works both horizontally and vertically, so use XLOOKUP in newer Excel. And keep month data in rows (the unpivot from Module 10), so PivotTables become easy too.
Ravindra Bagale's Tip – मराठी
HLOOKUP आजकाल कमी वापरला जातो – बऱ्याच students ना वाटतं की horizontal data साठी हाच एकमेव option आहे. XLOOKUP horizontal आणि vertical दोन्हीसाठी चालतो, त्यामुळे नवीन Excel मध्ये XLOOKUP वापरा. आणि months चा data rows मध्ये ठेवा (Module 10 मधला unpivot), म्हणजे PivotTables पण सोपे होतात.
Ravindra Bagale's Tip – हिंदी
HLOOKUP आजकल कम इस्तेमाल होता है – बहुत से students को लगता है कि horizontal data के लिए यही एकमात्र option है. XLOOKUP horizontal और vertical दोनों के लिए चलता है, इसलिए नए Excel में XLOOKUP इस्तेमाल करो. और months का data rows में रखो (Module 10 वाला unpivot), तो PivotTables भी आसान हो जाते हैं.
Practice task
Using HLOOKUP, show Nashik's target for the month typed in cell H1. Then do the same with XLOOKUP.
Loading practice tasks...
- Pune Target in Sep (Easy): Find Pune's target for "Sep".
- Pune Target in Aug (Easy): Find Pune's target for "Aug".
- Plants: Nashik in Q2 (Easy): Find the Nashik plant units for "Q2".
- Plants: Sambhaji Nagar in Q4 (Easy): Find the Sambhaji Nagar plant units for "Q4".
- Nashik Target in Nov (Medium): Find Nashik's target for "Nov".
- Month from a cell (Medium): Find Pune's target for the month in G1.
- Plants: Sambhaji Nagar in Q1 (Medium): Find the Sambhaji Nagar plant units for "Q1".
- Plants: Nashik in Q3 (Medium): Find the Nashik plant units for "Q3".
- Total of two months (Hard): Find Pune's Sep target plus Oct target.
- Plants: first half (Hard): Find the Nashik plant units for Q1 plus Q2.
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
