This reference lists 25 Excel logical functions, ordered by frequency of use, with high-frequency functions marked ⭐. Each function includes its syntax and an example.
| Function | Description | Example |
|---|---|---|
AND ⭐ |
Returns TRUE if all of its arguments are TRUE | =AND(A1>60,B1="M") — Returns TRUE when A1 > 60 and B1 = "M" |
FALSE ⭐ |
Returns the logical value FALSE | =FALSE() — Returns the logical value FALSE |
IF ⭐ |
Specifies a logical test to perform | =IF(A1>60,"Pass","Fail") — Returns "Pass" if A1 > 60, otherwise "Fail" |
IFERROR ⭐ |
Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula | =IFERROR(A1/B1,0) — Returns 0 if A1/B1 results in an error |
IFS ⭐ |
Checks whether one or more conditions are met and returns a value that corresponds to the first TRUE condition | =IFS(A1>=90,"Excellent",A1>=80,"Good",A1>=60,"Pass",TRUE,"Fail") — Returns the grade that matches the score |
NOT ⭐ |
Reverses the logic of its argument | =NOT(A1>60) — Returns FALSE when A1 > 60, otherwise TRUE |
OR ⭐ |
Returns TRUE if any argument is TRUE | =OR(A1>60,B1>60) — Returns TRUE when A1 > 60 or B1 > 60 |
TRUE ⭐ |
Returns the logical value TRUE | =TRUE() — Returns the logical value TRUE |
BYCOL |
Applies a LAMBDA to each column and returns an array of the results | =BYCOL(A1:C10,LAMBDA(col,SUM(col))) — Sums each column and returns an array of column totals |
BYROW |
Applies a LAMBDA to each row and returns an array of the results | =BYROW(A1:C10,LAMBDA(row,SUM(row))) — Sums each row and returns an array of row totals |
CHOOSECOLS |
Returns the specified columns from an array | =CHOOSECOLS(A1:C10,1,3) — Returns columns 1 and 3 |
CHOOSEROWS |
Returns the specified rows from an array | =CHOOSEROWS(A1:C10,1,3,5) — Returns rows 1, 3 and 5 |
DROP |
Excludes a specified number of rows or columns from the start or end of an array | =DROP(A1:A10,2) — Returns the array after dropping the first 2 rows |
IFNA |
Returns the value you specify if the expression resolves to #N/A, otherwise returns the result of the expression | =IFNA(VLOOKUP(A1,B:C,2,0),"Not found") — Shows "Not found" if VLOOKUP returns #N/A |
LAMBDA |
Create custom, reusable functions and call them by a friendly name | =LAMBDA(x,y,x*y)(5,6) — Creates a reusable Lambda function that computes a product, then calls it |
LET |
Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula | =LET(x,A1,y,B1,x+y) — Assigns A1 to x and B1 to y, then calculates x + y |
MAKEARRAY |
Returns a calculated array of a specified row and column size, by applying a LAMBDA | =MAKEARRAY(3,3,LAMBDA(r,c,r*c)) — Creates a 3x3 multiplication table array |
MAP |
Returns an array formed by mapping each value in the array(s) to a new value by applying a LAMBDA to create a new value | =MAP(A1:A5,LAMBDA(x,x*2)) — Multiplies each value in A1:A5 by 2 and returns a new array |
SWITCH |
Evaluates an expression against a list of values and returns the result corresponding to the first matching value. If there is no match, an optional default value may be returned | =SWITCH(A1,1,"Mon",2,"Tue","Other") — Returns the matching day of the week based on the value in A1 |
TAKE |
Returns a specified number of contiguous rows or columns from the start or end of an array | =TAKE(A1:A10,5) — Returns the first 5 rows of A1:A10 |
TOCOL |
Returns the array in a single column | =TOCOL(A1:C3) — Converts a 3x3 range into a single column |
TOROW |
Returns the array in a single row | =TOROW(A1:C3) — Converts a 3x3 range into a single row |
WRAPCOLS |
Wraps the provided row or column of values by columns after a specified number of elements | =WRAPCOLS(A1:A10,3) — Wraps 10 elements into a 2D array by column |
WRAPROWS |
Wraps the provided row or column of values by rows after a specified number of elements | =WRAPROWS(A1:A10,3) — Wraps 10 elements into a 2D array with 3 per row |
XOR |
Returns TRUE if an odd number of arguments are TRUE | =XOR(TRUE,FALSE,TRUE) — Returns the exclusive-or result — TRUE for an odd number of TRUEs |
From Online Toolbox (www.vba.net)