Ravindra BagaleCourses & study guides

4. Lookup Functions

By Last updated:

Chala mitrano, aaj aapan XLOOKUP aani tyache sagle bhau-bahin shikuya – VLOOKUP, HLOOKUP, INDEX + MATCH, XMATCH. Interview madhe sagalyat jast vicharla jaanara topic mhanje lookups – he khup important aahe. Ek table madhun dusrya table madhe mahiti kashi aanaychi, delivery fee slabs kase lavayche aani lookup errors kase sodvayche, he sagla aaj pakka karuya.

What you will learn in this module

  • VLOOKUP exact and approximate match, and the column-index trap
  • HLOOKUP for horizontal tables
  • INDEX + MATCH, including two-way lookups
  • XLOOKUP: if_not_found, match modes, search modes and returning many columns (Microsoft 365 / Excel 2021+)
  • XMATCH, delivery-fee slabs with approximate lookup, and fixing common lookup errors

Lookup table used in this module – sheet Stores, range A1:F8:

A: Store ID B: Platform C: City D: Area E: City Manager F: Manager Email
BLK-PUN-01 Blinkit Pune Kothrud Ravindra Bagale ravindra.bagale@example.com
BLK-PUN-02 Blinkit Pune Hinjewadi Shraddha Bagale shraddha.bagale@example.com
BLK-NSK-01 Blinkit Nashik College Road Zoya zoya@example.com
AMN-NGP-01 Amazon Now Nagpur Dharampeth Amir amir@example.com
BLK-KOP-01 Blinkit Kolhapur Rajarampuri Rani rani@example.com
AMN-SLP-01 Amazon Now Solapur Murarji Peth Salman salman@example.com
BLK-SBN-01 Blinkit Sambhaji Nagar CIDCO Raja raja@example.com

Concepts in this chapter

  1. 4.1VLOOKUP with Exact Match
  2. 4.2VLOOKUP Approximate Match and the Column-Index Trap
  3. 4.3HLOOKUP
  4. 4.4INDEX and MATCH
  5. 4.5Two-way Lookup (Row and Column)
  6. 4.6XLOOKUP Basics and if_not_found
  7. 4.7XLOOKUP Match Modes, Search Modes and Multiple Returns
  8. 4.8XMATCH
  9. 4.9Approximate Lookup: Delivery Fee Slabs
  10. 4.10Common Lookup Errors and Fixes

The chapter recap is at the end of the last concept page.