4.6 XLOOKUP Basics and if_not_found
Microsoft 365 / Excel 2021+.
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
| Formula | Result |
|---|---|
=XLOOKUP(G2, Stores!$A$2:$A$8, Stores!$C$2:$C$8) |
City of the store |
=XLOOKUP("Dharampeth", Stores!$D$2:$D$8, Stores!$A$2:$A$8) |
AMN-NGP-01 (left lookup, no trick needed) |
=XLOOKUP("BLK-PUN-09", Stores!$A$2:$A$8, Stores!$C$2:$C$8, "Not in master") |
Not in master |
Why XLOOKUP is better than VLOOKUP: exact match by default; separate lookup and return columns (no column counting, safe when columns move); looks left; built-in if_not_found; can search from the bottom; returns several columns at once; works vertically and horizontally.
Ravindra Bagale's Tip
XLOOKUP madhe lookup_array aani return_array chi length same pahije – A2:A8 aani C2:C9 dila tar #VALUE! yeto. Khup students ek range row 1 pasun aani dusri row 2 pasun ghetat. Doghi ranges same rows chya asavya. Ani file dusryala pathvaychi asel tar tyacha Excel XLOOKUP support karto ka te aadhi vichara.
Practice task
Replace your VLOOKUPs from 4.1 with XLOOKUP. Use if_not_found to show "Add to master" for unknown stores.