Truncates a number to a specified number of digits
POWER
Returns a number raised to a power
SQRT
Returns the square root of a number
ABS
Returns the absolute value of a number
MOD
Returns the remainder after division
PI
Returns the value of π
SIN
Returns the sine of an angle
COS
Returns the cosine of an angle
TAN
Returns the tangent of an angle
ASIN
Returns the arcsine of a number
ACOS
Returns the arccosine of a number
ATAN
Returns the arctangent of a number
ATAN2
Returns the arctangent of two numbers (x, y)
2. Statistical Functions
Function
Description
AVERAGE
Returns the average of numbers
AVERAGEIF
Returns the average of numbers that meet a condition
AVERAGEIFS
Returns the average of numbers that meet multiple conditions
COUNT
Counts the number of numeric values
COUNTA
Counts all non-empty cells
COUNTBLANK
Counts empty cells
COUNTIF
Counts cells that meet a condition
COUNTIFS
Counts cells that meet multiple conditions
MAX
Returns the maximum value in a range
MIN
Returns the minimum value in a range
MEDIAN
Returns the median of numbers
MODE
Returns the most frequent number
STDEV.P
Standard deviation for the entire population
STDEV.S
Standard deviation for a sample
VAR.P
Variance for the entire population
VAR.S
Variance for a sample
RANK.EQ
Returns the rank of a number
RANK.AVG
Returns the rank with average in case of ties
3. Logical Functions
Function
Description
IF
Returns one value if condition is TRUE, another if FALSE
AND
Returns TRUE if all conditions are TRUE
OR
Returns TRUE if any condition is TRUE
NOT
Reverses the logical value
IFERROR
Returns a value if no error, otherwise specified value
IFS
Checks multiple conditions in order
SWITCH
Evaluates expressions against a list of values
TRUE
Returns logical TRUE
FALSE
Returns logical FALSE
4. Text Functions
Function
Description
CONCAT
Combines text from multiple ranges or strings
TEXTJOIN
Joins text with a delimiter, ignoring blanks
LEFT
Returns the first characters of a string
RIGHT
Returns the last characters of a string
MID
Returns characters from the middle of a string
LEN
Returns the length of a string
TRIM
Removes extra spaces
UPPER
Converts text to uppercase
LOWER
Converts text to lowercase
PROPER
Capitalizes the first letter of each word
REPLACE
Replaces characters in a string
SUBSTITUTE
Replaces occurrences of text with new text
VALUE
Converts text to a number
FIND
Finds the position of text in a string (case-sensitive)
SEARCH
Finds the position of text (not case-sensitive)
5. Date & Time Functions
Function
Description
TODAY
Returns the current date
NOW
Returns the current date and time
DATE
Creates a date from year, month, day
TIME
Creates a time from hour, minute, second
DAY
Returns the day of a date
MONTH
Returns the month of a date
YEAR
Returns the year of a date
HOUR
Returns the hour of a time
MINUTE
Returns the minute of a time
SECOND
Returns the second of a time
EOMONTH
Returns the last day of the month
WORKDAY
Returns a date after adding working days
NETWORKDAYS
Counts working days between two dates
DATEDIF
Returns difference between two dates
6. Lookup & Reference Functions
Function
Description
VLOOKUP
Looks up a value vertically in a table
HLOOKUP
Looks up a value horizontally in a table
XLOOKUP
Advanced lookup (vertical or horizontal)
INDEX
Returns the value of a cell in a table based on row & column
MATCH
Returns the relative position of a value in a range
OFFSET
Returns a cell or range offset by rows and columns
ROW
Returns the row number
COLUMN
Returns the column number
ROWS
Counts the number of rows in a range
COLUMNS
Counts the number of columns in a range
CHOOSE
Returns a value from a list based on index number
HYPERLINK
Creates a clickable hyperlink
7. Financial Functions
Function
Description
PMT
Calculates loan payment
FV
Future value of an investment
PV
Present value of an investment
NPV
Net present value
IRR
Internal rate of return
RATE
Interest rate per period
PPMT
Principal portion of a loan payment
IPMT
Interest portion of a loan payment
CUMIPMT
Cumulative interest payment
CUMPRINC
Cumulative principal payment
8. Engineering Functions
Function
Description
COMPLEX
Returns a complex number
IMABS
Absolute value of a complex number
IMAGINARY
Imaginary coefficient of a complex number
IMREAL
Real coefficient of a complex number
DELTA
Checks equality of two numbers
CONVERT
Converts a number from one measurement unit to another
9. Information Functions
Function
Description
ISNUMBER
Checks if a value is a number
ISTEXT
Checks if a value is text
ISBLANK
Checks if a cell is blank
ISERROR
Checks if a value is any error
ISERR
Checks if a value is an error except #N/A
TYPE
Returns the type of a value
10. Database & Cube Functions
Function
Description
DSUM
Sum values that meet database criteria
DCOUNT
Count values in a database
DGET
Extract a single value from a database
DMAX
Maximum in database based on criteria
DMIN
Minimum in database based on criteria
CUBEMEMBER
Returns a member from cube
CUBEVALUE
Returns value from cube
CUBEMEMBERPROPERTY
Returns member property from cube
11. Dynamic Array & New Excel 365 Functions
Function
Description
FILTER
Returns filtered array based on condition
SORT
Sorts array of values
SORTBY
Sorts array by another array
UNIQUE
Returns unique values from a range
SEQUENCE
Generates a sequence of numbers
RANDARRAY
Returns array of random numbers
XMATCH
Returns position of a value in a range (better than MATCH)
12. LAMBDA & Custom Functions (Excel 365)
Function
Description
LAMBDA
Creates reusable custom functions
MAP
Applies a LAMBDA to each element in an array
REDUCE
Reduces an array to a single value using LAMBDA
MAKEARRAY
Creates an array using LAMBDA
BYROW
Applies LAMBDA row-wise
BYCOL
Applies LAMBDA column-wise
💡 Tip: With Excel 365, you can combine dynamic arrays and LAMBDA functions to create an unlimited number of custom calculations, so technically the “number of functions” is infinite if you include user-defined ones.
Here’s a detailed explanation of how to use the WRAPROWS and WRAPCOLS functions in Excel — these are part of Excel’s dynamic array functions available in Microsoft 365 and Excel 2021 onwards.
1. WRAPROWS Function in Excel
Purpose:
WRAPROWS reshapes a single row or column of data into a table-like structure with a specified number of values per row.
Syntax:
WRAPROWS(vector, wrap_count, [pad_with])
Parameters:
vector: The range or array to reshape (single row/column)
wrap_count: How many items per row
pad_with(optional): Value to fill in if the last row is incomplete
Example:
Given a list in A1:A10:
A1:A10 = {1,2,3,4,5,6,7,8,9,10}
Formula:
=WRAPROWS(A1:A10, 4)
Result:
1 2 3 4
5 6 7 8
9 10
With padding:
=WRAPROWS(A1:A9, 4, "NA")
Result:
1 2 3 4
5 6 7 8
9 NA NA NA
2. WRAPCOLS Function in Excel
Purpose:
WRAPCOLS reshapes data into a column-wise format, specifying how many values per column.
Syntax:
WRAPCOLS(vector, wrap_count, [pad_with])
Example:
List in A1:A10:
=WRAPCOLS(A1:A10, 4)
Result:
1 5 9
2 6 10
3 7
4 8
With padding:
=WRAPCOLS(A1:A9, 4, "N/A")
Result:
1 5 9
2 6 N/A
3 7 N/A
4 8 N/A
✅ Key Notes:
These functions are useful for layout transformation, preparing data for printing, visualization, or dashboards.
They work well with other dynamic functions like SEQUENCE, SORT, UNIQUE, etc.
🔍 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.
(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:
A
B
Product
Price
Apple
100
Banana
60
Mango
80
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.
The DROP function in Excel 365 is a dynamic array function that allows you to remove a specified number of rows or columns from the start or end of an array or range.
🔧 Syntax:
DROP(array, rows, [columns])
Argument
Description
array
The array or range of data to modify
rows
Number of rows to drop. Positive to drop from top, negative from bottom
[columns]
(Optional) Number of columns to drop. Positive to drop from left, negative from right
📘 Example 1: Drop Top 2 Rows
=DROP(A1:C5, 2)
➡️ Drops the first 2 rows, returns rows 3 to 5 from columns A to C.
📘 Example 2: Drop Last 1 Row and First 1 Column
=DROP(A1:C5, -1, 1)
➡️ Drops the last row and the first column.
📘 Example 3: Drop Last 2 Columns
=DROP(A1:D4, 0, -2)
➡️ Keeps all rows, removes the last 2 columns.
🧠 Interview-Based Questions (with answers)
Q1.What is the use of the DROP function in Excel 365?
A1. The DROP function is used to exclude a specific number of rows or columns from an array or range, returning the remaining values dynamically. It’s particularly helpful when cleaning data or adjusting tables on the fly.
Q2.Can the DROP function be used with ranges that include text data?
A2. Yes, the DROP function works with arrays that include text, numbers, dates, or any Excel-supported data types.
Q3.What will the result be if you use a negative value for the rows or columns arguments in DROP?
A3. A negative value for rows drops rows from the bottom. A negative value for columns drops columns from the right.
Q4.What happens if you use the DROP function on a range smaller than the number of rows or columns you try to drop?
A4. Excel will return a #CALC! error, indicating the drop exceeds the array bounds.
Q5.Can you combine DROP with other dynamic array functions like SORT or FILTER?
A5. Yes, DROP is often combined with functions like SORT, FILTER, TAKE, or UNIQUE to create powerful, flexible data transformations in Excel 365.
