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 |
=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.