17.3 Lookups

Q21. VLOOKUP vs XLOOKUP – what is the difference?

VLOOKUP searches only the first column of a range and returns a column by number, cannot look left, defaults to approximate match and breaks when columns are inserted. XLOOKUP (Microsoft 365 / Excel 2021+) uses separate lookup and return ranges, can look left, defaults to exact match, has a built-in if_not_found, can search last-to-first and can return several columns. For new work in Microsoft 365 I use XLOOKUP; for files shared with Excel 2016/2019 users I use INDEX-MATCH.

Q22. What are the advantages of INDEX-MATCH over VLOOKUP?

It can look left; it doesn't depend on a hard-coded column number, so inserting columns doesn't break it; it only references the two columns needed; and it supports two-way lookups with two MATCH functions. It works in all versions.

Q23. What are the limitations of VLOOKUP?

Lookup column must be leftmost; column index is hard-coded; returns only the first match; approximate match by default if the last argument is omitted; fails on text-vs-number or extra-space mismatches.

Q24. How do you do a two-way lookup?

=INDEX(B2:M7,MATCH("Nashik",A2:A7,0),MATCH("Mar-2026",B1:M1,0)), or a nested XLOOKUP: =XLOOKUP("Nashik",A2:A7,XLOOKUP("Mar-2026",B1:M1,B2:M7)).

Q25. When do you use approximate match?

For slab or band lookups sorted in ascending order – tax slabs, commission bands or delivery-fee slabs. With slabs 0→₹30, 99→₹25, 199→₹15, 499→₹0, an order of ₹250 returns ₹15.

Q26. Your VLOOKUP returns #N/A although the value is visible. Why?

Common causes: extra or non-breaking spaces, number stored as text on one side, different spelling/case variants with hidden characters, the lookup column not being the first column, or approximate/exact match confusion. I check with =LEN() and =ISNUMBER(), clean with TRIM/VALUE, and then retry.

Q27. How do you lookup with multiple criteria?

XLOOKUP with a Boolean array: =XLOOKUP(1,(tblStores[City]="Pune")*(tblStores[Platform]="FreshGo"),tblStores[Store ID]), or a helper key column (City&"|"&Platform) with a normal lookup, or INDEX-MATCH with the same Boolean array.

Q28. Can lookup functions be used for data cleaning?

Yes. A mapping table (for example "Aurangabad" → "Sambhaji Nagar", "pune" → "Pune") with XLOOKUP standardises values; lookups against a master list also reveal invalid or missing codes (#N/A or "Not found" flags).