XMATCH function searches for a specified item in an array or range of cells, and then returns the item's relative position.
The XMATCH function returns the relative position of an item in an array or range of cells. =XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])
PROPER
PROPER function is a Text function, and its purpose is to capitalize the first letter in each word of a text value that will remain in lowercase or convert to lowercase.
XLOOKUP function: Use =XLOOKUP("last", range, return_array) to get the value of the last non-empty cell1.
LOOKUP function: Use =LOOKUP("last", range, return_array) to find the last non-blank cell in a row2.
COUNTA and OFFSET functions: Use =COUNTA(range) to count the number of non-empty cells, and =OFFSET(A1, COUNTA(A:A)-1, 0) to get the last non-blank cell34.
ADDRESS and LOOKUP functions: Use =ADDRESS(LOOKUP(2, 1/(M:M<>""), ROW(M:M)), 13) to return the address of the last non-blank cell in column M
| Basic Functions & Operations | SUM & COUNT | ||
| A1+B1 | Addition | SUM(A1: A10) | Sum of a range |
| A1-B1 | Subtraction | AVERAGE(A1:A10) | Average of a range |
| A1*B1 | Multiplication | COUNT(A1:A10) | Count of numeric entries |
| A1/B1 | Division | COUNTIF(A1:A10, ">10") | Count cells that meet a condition |
| A1*2 | Exponentation | COUNTIFS | Counts the number of cells in a range where one or more conditionsa are met |
| MOD(A1, B1) | Remainder of division | SUMIF(A1: A10, ">10", B1: B10) | Sum based on a condition |
| Logical Functions | MIN(A1:A10) | Calculates the smallest number in a range | |
| IF(A1> 10, "Yes", "No") | Conditional Statement | MEDIAN | Finds the median value in a group of values |
| AND(A1> 0, B1 < 5) | Logical AND | MAX | Calculates the highest number a range |
| OR(A1 > 10, B1 < 5) | Logical OR | Text Functions | |
| NOT(A1 = 10) | Logical NOT | CONCATENATE(A1, "", B1) | Join text strings |
| IFERROR(A1/B1, "Error") | Handle errors gracefully | TEXT(A1, "0.00") | Format number as text |
| Date & Time | LEFT(A1, 3) | Extract Left Most characters | |
| Today() | Current date | RIGHT(A1, 3) | Extract Right Most characters |
| NOW() | Currnet date and time | MID(A1, 2, 3) | Extract chracters from the middle |
| Lookup Functions | LEN(A1) | Length of a Text String | |
| VLOOKUP | Vertifically searches for a value in a table | FIND("text", A1) | Find position of text |
| INDEX | A lookup function that searches a table both vertically and horizontally | SUBSTITUTE(A1, "old", "new") | Replace text in a string |
| XLOOKUP | An updated version of VLOOKUP available in newer Excel versions | UPPER | Converts a string of text to all uppercase |
| HLOOKUP | Horizontally searches for a value in a table | LOWER | Converts a string of text to all lowercase |
| MATCH | Returns the location of a value in a sequence | VALUE | Converts a text to a value |
| OFFSET | Moves the cell reference by the number of given rows and/or columns | ||
No comments:
Post a Comment