Training By Himanshu

Tag: Advanced Excel Functions

  • How to Use BYROW and BYCOL Functions in Excel 365 with Practical Examples

    🧠 What Are BYCOL and BYROW Functions in Excel 365?

    BYCOL and BYROW are part of the Lambda helper functions in Excel 365. These functions allow you to apply custom logic across columns or rows of a range or array, making them incredibly useful for dynamic and reusable calculations.

    🔹 1. BYROW Function

    ✅ Purpose:

    Processes data row by row, applying a specified Lambda function to each row.

    📘 Syntax:

    excelCopyEdit=BYROW(array, lambda(row))
    • array: The data range you want to process.
    • lambda(row): A custom calculation to perform on each row.

    🧪 Example: Sum each row in a range

    You have this data in cells A2:C4:

    ABC
    235
    142
    627

    👉 Formula:

    excelCopyEdit=BYROW(A2:C4, LAMBDA(r, SUM(r)))

    ✅ Output:

    Sum
    10
    7
    15

    Each row is summed individually and spilled vertically.

    🔹 2. BYCOL Function

    ✅ Purpose:

    Processes data column by column, applying a specified Lambda function to each column.

    📘 Syntax:

    excelCopyEdit=BYCOL(array, lambda(column))
    • array: The data range you want to process.
    • lambda(column): A custom calculation to perform on each column.

    🧪 Example: Find the average of each column

    Same data in A2:C4:

    ABC
    235
    142
    627

    👉 Formula:

    excelCopyEdit=BYCOL(A2:C4, LAMBDA(c, AVERAGE(c)))

    ✅ Output:

    Average
    3.0
    3.0
    4.67

    Each column’s average is calculated and spilled horizontally.

    🔁 When to Use BYROW and BYCOL?

    Use CaseUse Function
    Sum or average of each rowBYROW
    Custom logic applied to each columnBYCOL
    Conditional check row-wiseBYROW + IF
    Min/max/median by columnBYCOL

    💡 More Practical Examples

    🎯 Count how many values > 3 in each row:

    excelCopyEdit=BYROW(A2:C4, LAMBDA(r, COUNTIF(r, ">3")))

    🎯 Find max value in each column:

    excelCopyEdit=BYCOL(A2:C4, LAMBDA(c, MAX(c)))

    ⚠️ Requirements

    • Available in Excel 365 and Excel 2021 only
    • Must use LAMBDA function inside

    🚀 Want to Automate This Logic?

    If you’re excited by what BYCOL and BYROW can do with formulas, imagine how much more powerful Excel becomes when you can automate this logic using VBA macros.

    Instead of manually applying formulas, you could:

    • Automatically summarize each row/column with a button click
    • Dynamically format top values
    • Export row/column summaries to reports

    🎓 Master Excel Automation with VBA (Beginner-Friendly)

    📘 Mastering Excel Automation – Excel VBA Training Course

    🔑 Why Learn VBA?

    • Eliminate repetitive tasks
    • Build powerful Excel tools
    • Automate complex logic (like BYROW/BYCOL) programmatically

    🎬 Course Highlights:

    • 42 easy-to-follow videos
    • 4 hours 8 minutes total
    • ₹441 only (Limited-time offer, originally ₹1,299)
    • Lifetime access

    🎯 Designed for non-programmers and Excel enthusiasts alike!

    🔗 👉 Enroll today and start automating Excel your way

    On sale products

  • XLOOKUP Function in Excel 365: Complete Guide with Examples and Top 20 Interview Questions

    🔍 How to Use the XLOOKUP Function in Excel 365 — Detailed Guide

    ✅ What is XLOOKUP?

    XLOOKUP is a powerful lookup function introduced in Excel 365 and Excel 2021 to replace older functions like VLOOKUP, HLOOKUP, and even INDEX + MATCH. It can search horizontally or vertically, supports approximate/partial matches, and even returns custom messages when no match is found.

    📌 Syntax:

    XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
    ArgumentDescription
    lookup_valueThe value to search for
    lookup_arrayThe range or array to search in
    return_arrayThe range or array to return data from
    if_not_found(Optional) Value to return if no match is found
    match_mode(Optional) 0 = exact match (default), -1 = exact or next smaller, 1 = exact or next larger, 2 = wildcard
    search_mode(Optional) 1 = search from first to last (default), -1 = search from last to first

    🧪 Basic Example:

    You have the following data:

    AB
    ProductPrice
    Apple100
    Banana60
    Mango80

    To find the price of Mango:

    =XLOOKUP("Mango", A2:A4, B2:B4)

    ➡️ Result: 80

    🧪 Example with if_not_found:

    =XLOOKUP("Orange", A2:A4, B2:B4, "Not Available")

    ➡️ Result: Not Available (because “Orange” doesn’t exist)

    🧪 Example using wildcard match:

    =XLOOKUP("*man*", A2:A4, B2:B4, , 2)

    ➡️ This matches any product containing “man” (e.g., “Mango”)

    🧪 Reverse Lookup (Bottom to Top):

    =XLOOKUP("Mango", A2:A4, B2:B4, , 0, -1)

    ➡️ Searches from bottom to top. Useful if the latest entry is preferred.

    🧠 20 Interview-Based Questions on XLOOKUP with Answers

    Q1. What is XLOOKUP in Excel?
    A1. XLOOKUP is a modern lookup function that replaces older functions like VLOOKUP and HLOOKUP. It can search vertically or horizontally and offers more flexibility.

    Q2. How is XLOOKUP better than VLOOKUP?
    A2. XLOOKUP allows lookup to the left, supports default return on no match, wildcards, and reverse searches, which VLOOKUP cannot do.

    Q3. Can XLOOKUP search horizontally?
    A3. Yes. You can use it like HLOOKUP by selecting rows instead of columns.

    Q4. What happens if the lookup value is not found?
    A4. If you specify the if_not_found parameter, that value is returned. Otherwise, Excel returns a #N/A error.

    Q5. How can you use XLOOKUP for an exact match?
    A5. Either omit the match_mode (default is exact) or explicitly set it to 0.

    Q6. Can XLOOKUP return an entire row or column?
    A6. Yes, it supports dynamic arrays, so it can return multiple values from a row or column.

    Q7. What does match_mode = 2 mean?
    A7. It enables wildcard matching using * (any number of characters) or ? (single character).

    Q8. What is the purpose of the search_mode parameter?
    A8. It controls the search direction: 1 = top to bottom (default), -1 = bottom to top.

    Q9. Is XLOOKUP case-sensitive?
    A9. No, XLOOKUP is not case-sensitive by default.

    Q10. Can XLOOKUP replace INDEX + MATCH?
    A10. Yes, and it’s simpler to write and understand.

    Q11. What’s the difference between XLOOKUP and LOOKUP?
    A11. LOOKUP is an older function requiring sorted data; XLOOKUP doesn’t and is more robust.

    Q12. What is returned if multiple matches are found?
    A12. XLOOKUP returns the first match, unless search_mode is set to -1 (then it returns the last match).

    Q13. Can XLOOKUP handle blank cells?
    A13. Yes. It will match blank cells if "" is used as the lookup_value.

    Q14. Can XLOOKUP be nested with other functions?
    A14. Yes, it works well inside other functions like IF, SUM, etc.

    Q15. How does XLOOKUP behave in arrays with errors?
    A15. It stops at the first error unless error handling (like IFERROR) is added.

    Q16. Is XLOOKUP available in Excel 2016 or 2019?
    A16. No. XLOOKUP is only available in Excel 365 and Excel 2021.

    Q17. Can XLOOKUP search from right to left?
    A17. Yes, it’s not restricted by column order like VLOOKUP.

    Q18. How to use XLOOKUP for range lookups (approximate match)?
    A18. Set match_mode to -1 (for next smaller) or 1 (for next larger).

    Q19. Can you perform two-way lookups using XLOOKUP?
    A19. Yes. Combine two XLOOKUPs — one for row and one for column.

    Q20. How does XLOOKUP handle dynamic named ranges or structured tables?
    A20. It works seamlessly with dynamic arrays, tables, and named ranges.

    Top rated products

  • Mastering VLOOKUP and HLOOKUP in Excel: A Complete Guide with Examples

    Mastering VLOOKUP and HLOOKUP in Excel: A Complete Guide with Examples

    ✅ What is VLOOKUP in Excel?

    VLOOKUP stands for Vertical Lookup. It searches for a value in the first column of a table and returns a value in the same row from another column.

    Syntax:

    VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

    Arguments:

    • lookup_value: The value to search for.
    • table_array: The table range to search within.
    • col_index_num: The column number in the table from which to retrieve the value.
    • range_lookup: Optional. TRUE for approximate match, FALSE for exact match.

    ✅ VLOOKUP Example:

    Imagine this table in range A2:C6:

    Employee IDNameDepartment
    101RajHR
    102SimranIT
    103AmanMarketing
    104PreetiFinance
    105RameshAdmin

    🔍 Goal: Find the Department of Employee ID 103.

    🧮 Formula:

    =VLOOKUP(103, A2:C6, 3, FALSE)

    ✅ Output:

    Marketing

    💡Why? VLOOKUP searched for 103 in column A, found it in row 4, then returned the value in the 3rd column of that row (C4).

    ✅ What is HLOOKUP in Excel?

    HLOOKUP stands for Horizontal Lookup. It searches for a value in the first row of a table and returns a value in the same column from another row.

    Syntax:

    HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

    Arguments:

    • lookup_value: The value to find in the first row.
    • table_array: The range that contains the data.
    • row_index_num: The row number in the table from which to return a value.
    • range_lookup: Optional. TRUE for approximate match, FALSE for exact match.

    ✅ HLOOKUP Example:

    Imagine this table in range A1:F3:

    ID101102103104105
    NameRajSimranAmanPreetiRamesh
    DeptHRITMarketingFinanceAdmin

    🔍 Goal: Find the Name of Employee ID 104.

    🧮 Formula:

    =HLOOKUP(104, A1:F3, 2, FALSE)

    ✅ Output:

    Preeti

    💡Why? HLOOKUP searched for 104 in row 1, found it in column E, and returned the value in the 2nd row of that column (E2).

    🆚 Key Differences: VLOOKUP vs HLOOKUP

    FeatureVLOOKUPHLOOKUP
    OrientationVertical (columns)Horizontal (rows)
    Lookup inFirst columnFirst row
    Output fromA specified columnA specified row
    Use caseWhen data is arranged verticallyWhen data is arranged horizontally

    🔄 Tips:

    • Use FALSE in range_lookup to ensure exact matches.
    • Use named ranges or TABLES for dynamic data.
    • VLOOKUP cannot look left. Use INDEX-MATCH for more flexibility.

    🔹 Job Interview Questions on VLOOKUP & HLOOKUP

    ✅ Basic Level

    1. What is the difference between VLOOKUP and HLOOKUP in Excel?
      (Expected: VLOOKUP searches vertically, HLOOKUP searches horizontally.)
    2. What does the col_index_num in VLOOKUP do?
      (Expected: It specifies the column number from which the value is returned.)
    3. What happens if range_lookup is set to TRUE vs FALSE in VLOOKUP/HLOOKUP?
      (Expected: TRUE gives approximate match, FALSE gives exact match.)
    4. Can VLOOKUP return values to the left of the lookup column? Why or why not?
      (Expected: No, because VLOOKUP can only return values from columns to the right.)
    5. Write a VLOOKUP formula to fetch the salary of Employee ID 102 from a given table.
      (Expect the candidate to form a valid VLOOKUP formula based on assumed columns.)

    ✅ Intermediate Level

    1. What error do you get if VLOOKUP cannot find the lookup value? How do you handle it?
      (Expected: #N/A error. Use IFERROR or IFNA to handle it gracefully.)
    2. What are the limitations of VLOOKUP, and how can they be overcome?
      (Expected: Can’t search left, slower in large datasets; can use INDEX-MATCH instead.)
    3. When would you prefer HLOOKUP over VLOOKUP? Give a practical example.
      (Expected: When data is structured in rows instead of columns — e.g., monthly sales in a horizontal table.)

    ✅ Advanced Level

    1. How would you dynamically look up data when the column index keeps changing?
      (Expected: Use MATCH() inside VLOOKUP or switch to INDEX-MATCH.) Example: =VLOOKUP("Product A", A1:D10, MATCH("Price", A1:D1, 0), FALSE)
    2. Can you perform a case-sensitive lookup using VLOOKUP or HLOOKUP?
      (Expected: No, they are not case-sensitive. Use INDEX, MATCH, EXACT, or array formulas for case-sensitive search.)

    Here’s your Excel practice file for VLOOKUP and HLOOKUP, complete with data and instructions:

    VLOOKUP_HLOOKUP_PracticeDownload

    📘 Contents:

    • VLOOKUP_Data: A vertical table to practice VLOOKUP.
    • HLOOKUP_Data: A horizontal table to practice HLOOKUP.
    • Instructions: A guide on how to use the file for practice.

    Watch the Video on Vlookup and Hlookup

    Related Products