Excel MAP Function
Chala mitrano — today we learn MAP. Read Why → What → How, then try the practice list.
चला मित्रांनो — आज MAP शिकू. Why → What → How वाचा, मग practice करा.
चलो दोस्तों — आज MAP सीखते हैं। Why → What → How पढ़ो, फिर practice करो।
Purpose
Apply a LAMBDA to every value in one or more arrays and spill the results.
Return value
An array the same shape as the mapped inputs (or combined shape when multiple arrays).
Syntax
Arguments
| Argument | Required? | Description |
|---|---|---|
array1 | Required | First array or range to walk through. |
array2… | Optional | More arrays of the same size (paired by position). |
lambda | Required | A LAMBDA that receives one value from each array and returns a result. |
Version
Microsoft 365. Needs LAMBDA. Not in Excel 2019 / 2016.
How to use — Why → What → How
Why: You want to change every price or every score with the same rule, without dragging a formula.
What: MAP walks each item, runs your LAMBDA, and spills a new array.
How — numbered steps:
- Write a small LAMBDA that takes one value, example: LAMBDA(x, x*1.18).
- Wrap it: =MAP(B2:B6, LAMBDA(x, x*1.18)).
- Press Enter and clear cells below/right so the spill fits.
- For two arrays: =MAP(qty, price, LAMBDA(q,p, q*p)).
- Name the LAMBDA in Name Manager if you reuse it often.
Ravindra Bagale's Tip
Friends, keep the LAMBDA short. Long MAP bodies are hard to read — use LET inside the LAMBDA if needed.
Ravindra Bagale's Tip – मराठी
मित्रांनो, LAMBDA छोटी ठेवा. लांब MAP अवघड वाचता येते — गरज असल्यास LAMBDA मध्ये LET वापरा.
Ravindra Bagale's Tip – हिंदी
दोस्तों, LAMBDA छोटी रखो। लंबी MAP पढ़ना मुश्किल — ज़रूरत हो तो LAMBDA में LET इस्तेमाल करो।
Ravindra Bagale's Tip
MAP needs equal-sized arrays when you pass more than one. Mismatch → #VALUE!.
Ravindra Bagale's Tip – मराठी
एकपेक्षा जास्त arrays दिल्यास आकार समान हवा. नाहीतर #VALUE!.
Ravindra Bagale's Tip – हिंदी
एक से ज़्यादा arrays हों तो साइज़ समान चाहिए। नहीं तो #VALUE!।
Worked examples
1) Add 18% GST to a price list (₹)
Nashik kirana: prices in B2:B4.
| Item | Price |
|---|---|
| Atta | 48 |
| Oil | 160 |
| Soap | 35 |
Result: 56.64, 188.8, 41.3
What it means: Each price × 1.18 spills down.
2) Line totals from qty × price
Priya packs boxes: qty C2:C4, price D2:D4.
Result: Product per row
What it means: Two arrays, one LAMBDA with two parameters.
3) UK cafe tips (£)
Emma adds 10% tip on bills E2:E3.
Result: Rounded tip-inclusive totals
What it means: Combine MAP with ROUND inside LAMBDA.
4) Pass / Fail labels
Scores F2:F5; pass if ≥40.
Result: Pass/Fail spill
What it means: MAP can return text too.
5) Clean trim each name
Names with spaces in A2:A6.
Result: Trimmed names
What it means: Same idea as dragging TRIM, but one spill.
Common errors
Practice tasks (10+)
- Task 1. Map prices A2:A8 × 1.05 (5% hike).Hint: try on a tiny range first
- Task 2. Map two columns qty and rate into line totals.Hint: try on a tiny range first
- Task 3. Map scores to Grade: ≥75 A, ≥50 B, else C.Hint: try on a tiny range first
- Task 4. Map a list of city names to UPPER.Hint: try on a tiny range first
- Task 5. Map amounts in £ to ₹ using a rate cell (absolute ref inside LAMBDA carefully).Hint: try on a tiny range first
- Task 6. Wrap MAP in LET for the rate name.Hint: try on a tiny range first
- Task 7. Explain why MAP beats dragging for interviews.Hint: try on a tiny range first
- Task 8. Cause a #SPILL! on purpose, then clear the block.Hint: try on a tiny range first
- Task 9. Map with three arrays (a+b+c) if your build allows.Hint: try on a tiny range first
- Task 10. Bonus: MAP + TEXT to format as ₹#,##0.00.Hint: try on a tiny range first
Quick quiz
Answers: Q1=True, Q2=False, Q3=True, Q4=True
Related: LAMBDA · LET · GROUPBY · Function hub · Learning path