Training By Himanshu

Tag: Excel Interview Questions

  • EXPAND Function in Excel 365 – Resize Arrays with Ease

    🔍 What is the EXPAND Function in Excel 365?

    The EXPAND function is a dynamic array function introduced in Excel 365. It allows you to resize an array to a specified number of rows and columns by adding empty cells or a custom value as needed.

    Think of it as a way to force a range into a specific shape, useful when building dynamic templates, padding arrays, or preparing structured data outputs.

    🔧 Syntax

    =EXPAND(array, rows, columns, [pad_with])
    ArgumentDescription
    arrayThe original array to expand
    rowsThe total number of rows desired in the output
    columnsThe total number of columns desired
    pad_with(Optional) The value to use for padding if the array is smaller than the specified size (default is blank)

    ✅ Examples of EXPAND in Excel

    🔹 Example 1: Expand a 2×2 Array to 4×4 with Blanks

    =EXPAND({1,2;3,4}, 4, 4)

    ✅ Output:

    1   2   ""  ""
3   4   ""  ""
""  ""  ""  ""
""  ""  ""  ""

    🔹 Example 2: Expand with a Custom Padding Value

    =EXPAND({1,2;3,4}, 3, 5, 0)

    ✅ Output:

    1   2   0   0   0  
3   4   0   0   0  
0   0   0   0   0

    🔹 Example 3: Use with VSTACK or HSTACK

    You can combine EXPAND with VSTACK to align data nicely:

    =EXPAND(VSTACK({1,2}, {3,4}), 5, 2, "-")

    🔹 Example 4: Prepare Fixed Template Output

    Use EXPAND to standardize report sections, e.g., always show 10 rows in a report, even if data has fewer:

    =EXPAND(A2:B4, 10, 2, "N/A")

    🔹 Example 5: Resize Named Ranges for Dashboards

    Create a uniform input structure for dashboards that doesn’t break when data is missing.

    🧠 Why Use EXPAND?

    • Ensures consistent array size for formulas or visualizations
    • Helps in report automation
    • Pairs well with functions like DROP, TAKE, VSTACK, HSTACK
    • Great for data transformation pipelines

    ❓ 5 Interview-Based Questions on EXPAND

    1. What is the purpose of the EXPAND function in Excel 365?

    Expected Answer: To resize an array to a specified number of rows and columns, filling in missing cells with blank or a defined value.

    2. What will this formula return?

    =EXPAND({10,20;30,40}, 3, 3, "X")

    Answer:

    10   20   X  
30   40   X  
X    X    X

    3. How can EXPAND be used to create a fixed-size export template?

    Expected Answer: By padding data with a default value up to a known row/column count, ensuring uniformity in exports or dashboard feeds.

    4. What happens if the array passed to EXPAND is already larger than the specified size?

    Answer: Excel will not truncate the array — it will simply return the full array. EXPAND only pads; it doesn’t shrink.

    5. Write a formula to expand a 2×2 array into a 4×4 array using the value “NA” as filler.

    =EXPAND({1,2;3,4}, 4, 4, "NA")

    🎓 Learn More Excel 365 Power Functions

    Ready to master advanced Excel functions like EXPAND, REDUCE, SCAN, LAMBDA, and more?

    👉 Join My Excel Mastery Course
    ✅ Covers automation, dynamic reports, dashboards, and real-life use cases.

    Top rated products

  • Mastering the MAP Function in Excel 365 – Explained with Examples

    🧠 What is the MAP Function in Excel 365?

    The MAP function is a Lambda helper function in Excel 365 that lets you apply a custom formula (Lambda) to each element of one or more arrays.

    It’s similar to the “map” concept in programming — you pass in arrays, and MAP processes each corresponding item across those arrays using your custom logic.

    🔧 Syntax of MAP

    =MAP(array1, [array2], ..., lambda(value1, [value2], ..., calculation))
    ParameterDescription
    array1The first array to apply the function to
    array2…(Optional) Additional arrays
    lambdaA custom function that defines what to do with each item in the array(s)

    ✅ Key Features

    • Processes each item in an array (or matching items from multiple arrays)
    • Great for row-wise transformations, custom logic, or string manipulation
    • Fully dynamic and compatible with spilled ranges
    • Ideal for creating reusable custom logic without VBA

    🔍 Examples of MAP in Action

    🔹 Example 1: Add 10 to Each Number

    If A1:A5 = {5, 10, 15, 20, 25}

    =MAP(A1:A5, LAMBDA(x, x + 10))

    ✅ Output: {15, 20, 25, 30, 35}

    🔹 Example 2: Combine Names from Two Columns

    AB
    RaviSharma
    PriyaMehta
    AkashVerma
    =MAP(A2:A4, B2:B4, LAMBDA(f, l, f & " " & l))

    ✅ Output:
    Ravi Sharma
    Priya Mehta
    Akash Verma

    🔹 Example 3: Apply IF Logic to Array

    Add 100 if value > 50, else keep it unchanged:

    =MAP(A1:A5, LAMBDA(x, IF(x > 50, x + 100, x)))

    🔹 Example 4: Format Text to Title Case (First letter capital)

    Assume names in A2:A4:

    =MAP(A2:A4, LAMBDA(n, UPPER(LEFT(n,1)) & LOWER(MID(n,2,LEN(n)))))

    ✅ Transforms “rAVI” into “Ravi”

    📌 Real-World Use Cases

    • Process data row-by-row or column-by-column
    • Apply different logic to multiple inputs
    • Create advanced conditional formatting via formulas
    • Perform string cleanup, name formatting, score adjustments
    • Replace helper columns with dynamic logic

    ❓ Interview-Style Question

    Question:
    You’re given two arrays:
    Array1 = {10, 20, 30, 40}
    Array2 = {1, 2, 3, 4}

    Write a formula using MAP that multiplies each pair of values from Array1 and Array2.

    Answer:

    =MAP({10, 20, 30, 40}, {1, 2, 3, 4}, LAMBDA(a, b, a * b))

    ✅ Output: {10, 40, 90, 160}

    🎓 Want to Master Lambda & MAP in Real Scenarios?

    Learn how to use MAP, REDUCE, SCAN, LAMBDA, and other Excel 365 functions with dashboards, custom tools, and real case studies in:

    👉 Mastering MS Excel – A Comprehensive Training Course

    Top rated products

  • What is the REDUCE Function in Excel 365?

    The REDUCE function is a Lambda helper function introduced in Excel 365. It allows you to loop through an array, applying a formula to each element, and accumulate a single result (like a running total or combined value).

    Think of it like a fold or accumulator function in programming — it starts with an initial value and “reduces” an array step-by-step using logic you define.

    🔧 Syntax

    excelCopyEdit=REDUCE(initial_value, array, lambda(accumulator, value))
    ArgumentDescription
    initial_valueThe starting value (can be 0, “”, etc.)
    arrayThe array you want to process
    lambdaA custom formula with two parameters: accumulator (running total) and value (current array element)

    ✅ Examples of REDUCE

    🔹 Example 1: Sum All Numbers in an Array

    excelCopyEdit=REDUCE(0, A1:A5, LAMBDA(a, v, a + v))
    • A1:A5 contains {10, 20, 30, 40, 50}
    • Output: 150

    🔁 Starts with 0, then adds each value:
    0 + 10 → 10 + 20 → 30 + 30 → 60 + 40 → 100 + 50 = 150

    🔹 Example 2: Concatenate All Text Values

    excelCopyEdit=REDUCE("", A1:A4, LAMBDA(a, v, a & v))
    • A1:A4 contains: {"Hi", " ", "there", "!"}
    • Output: "Hi there!"

    🔹 Example 3: Count Values Greater Than 50

    excelCopyEdit=REDUCE(0, A1:A5, LAMBDA(a, v, a + IF(v > 50, 1, 0)))
    • If A1:A5 = {40, 55, 60, 30, 80}
    • Output: 3 (since 55, 60, and 80 > 50)

    🔹 Example 4: Multiply All Values

    excelCopyEdit=REDUCE(1, A1:A4, LAMBDA(a, v, a * v))
    • A1:A4 = {2, 3, 4, 5}
    • Output: 120

    🔹 Example 5: Create a Dash-Separated List

    excelCopyEdit=REDUCE("", A1:A3, LAMBDA(a, v, IF(a = "", v, a & "-" & v)))
    • A1:A3 = {Jan, Feb, Mar}
    • Output: "Jan-Feb-Mar"

    🎯 Why is REDUCE Useful?

    • Performs row-by-row logic without VBA or helper columns
    • Great for cumulative totals, conditional aggregations, and string building
    • Works well inside LAMBDA-based custom functions

    ❓ 3 Interview-Based Questions on REDUCE

    1. What is the key difference between REDUCE and SCAN in Excel 365?

    (Expected Answer: REDUCE returns only the final accumulated result, while SCAN returns all intermediate steps.)

    2. How would you use REDUCE to count how many numbers are even in a range?

    (Hint: Use IF(MOD(value,2)=0, 1, 0) inside LAMBDA and accumulate the result.)

    3. What is the purpose of the initial_value argument in REDUCE?

    (Expected: It defines the starting point of the accumulation. For summing, it would be 0; for concatenating, it may be an empty string.)

    Top rated products

  • Master the XMATCH Function in Excel 365

    📘 What is the XMATCH Function in Excel?

    The XMATCH function is a modern alternative to MATCH, introduced in Excel 365 and Excel 2021. It returns the relative position of an item in a row or column. Unlike MATCH, it supports reverse search, wildcard matching, and exact or approximate search modes.

    🔧 Syntax of XMATCH

    =XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])
    ParameterDescription
    lookup_valueThe value you want to search for
    lookup_arrayThe range or array to search in
    match_mode(Optional) 0 = exact (default), -1 = exact or next smaller, 1 = exact or next larger, 2 = wildcard match
    search_mode(Optional) 1 = first-to-last (default), -1 = last-to-first, 2 = binary ascending, -2 = binary descending

    ✅ Key Features of XMATCH

    • Supports vertical and horizontal lookups
    • Can search from last to first (search_mode = -1)
    • Allows wildcard characters (match_mode = 2)
    • Works with arrays and spilled ranges
    • Better compatibility with dynamic arrays

    🧪 Examples

    🔹 Example 1: Basic Exact Match

    =XMATCH("Priya", A2:A10)

    Searches for “Priya” in the list and returns the position where it’s found.

    ✅ If “Priya” is in cell A5 (4th position in A2:A10), the result is 4.

    🔹 Example 2: Wildcard Match

    =XMATCH("P*", A2:A10, 2)

    Returns the first item starting with “P”.

    ✅ Useful for partial string lookups.

    🔹 Example 3: Reverse Search

    =XMATCH("Complete", A2:A10, 0, -1)

    Searches bottom-up for “Complete”.

    🔹 Example 4: Approximate Match

    If you have numbers like 50, 60, 70, and you’re looking for 65:

    =XMATCH(65, A2:A10, 1)

    Returns the position of the next larger number (70).

    🔹 Example 5: Use with INDEX for Advanced Lookup

    =INDEX(B2:B10, XMATCH("Ravi", A2:A10))

    Finds Ravi in column A and returns corresponding value from column B.

    ✅ Powerful alternative to VLOOKUP or INDEX+MATCH.

    🎓 Common Use Cases

    • Find row/column numbers dynamically
    • Combine with INDEX for 2D lookups
    • Reverse search to find last matching item
    • Match using wildcards like "*Report" or "Jan???"
    • Create dynamic dashboards or filters

    ❓ 5 Interview-Based Questions on XMATCH

    1. What is the key difference between XMATCH and MATCH in Excel? (Expected: XMATCH supports reverse search, wildcards, exact/approximate modes, and works with dynamic arrays.)
    2. How would you find the last occurrence of a value in a list using XMATCH? (Hint: Use search_mode = -1)
    3. What does the following formula return? =XMATCH(75, A2:A6, -1) (Expected: Returns the position of the largest number less than or equal to 75.)
    4. Can XMATCH be used with INDEX to replicate VLOOKUP? Provide an example. (Yes, e.g., =INDEX(B2:B10, XMATCH("ItemName", A2:A10)))
    5. Explain how to use XMATCH for partial matches using wildcards. (Set match_mode = 2, e.g., =XMATCH("Jan*", A2:A10, 2))

    📌 Final Thoughts

    XMATCH is more powerful and flexible than MATCH and a great fit for modern Excel tasks involving dynamic lookups. If you’re preparing for interviews or building advanced dashboards, mastering XMATCH can save time and simplify your logic.

    🚀 Want to Master Excel 365 Lookups?

    Enroll in my in-depth Excel training course covering:

    • XMATCH, XLOOKUP, INDEX-MATCH, FILTER, LET, LAMBDA
    • Dashboards, automation, case studies

    👉 Mastering MS Excel – A Comprehensive Training Course

    On sale products