Bottom line: Learn how to use the COUNTIF function instead of VLOOKUP to determine if a value exists in a list or range.
Skill level: Beginner
Problem – I want to check and see if the names in column B exist in this long list of names in column E.
There are a lot of different ways we can solve this problem with formulas in Excel. You might be inclined to use a VLOOKUP formula, and that would work just fine.
However, there are a few advantages to using the COUNTIF function instead.
- It's faster
- Easier
- And less prone to errors.
So let's learn how COUNTIF works.
Video Tutorial on How to Use COUNTIF
Here is a video from The Ultimate Lookup Formulas Course that explains how to use COUNTIF instead of VLOOKUP.
*Double-click the video to watch full screen.
There are over 100 short videos just like the one above included in the Ultimate Lookup Formulas Course.
This course has been designed to help you master Excel's most important functions and formulas in an easy step-by-step manner.
The Ultimate Lookup Formulas Course is now part of our comprehensive Elevate Excel Training Program.
Click Here to Learn More About the Elevate Excel
Download the Example File
Download the Excel file to follow along.
COUNTIF-Function-Check-If-A-Value-Exists.zipDownload
The COUNTIF Function Explained
COUNTIF is a very simple function. It's job is to search a range for a value and tell you how many cells match that value.
In the example below I am using a COUNTIF function to return the number of times the word “Surf” exists in the range. The result is 3.
Arguments: COUNTIF only has two arguments (Range, Criteria).
- Range – this is the range we want to search for the lookup value (Criteria).
- Criteria – this is the value we want to find.
Rules: By default COUNTIF is going to look for an exact match of each cell's entire value. You can add wild cards and conditional/relational operators to the criteria to look for partial matches.
Result: COUNTIF returns a numerical value for the number of cells that matched the criteria in the range. If the COUNTIF returns a number greater than or equal to 1, then that means the value exists in the list. If COUNTIF returns a zero then there are no cells that match the criteria.
So back to our original problem, we can use COUNTIF to see how many times the names exist in the list.
The cells in C2:C4 contain the COUNTIF function.
When COUNTIF returns a zero (0) this means the value does not exist. So the result in cell C4 tells us that the name Bob Stevens does not exist in the list.
Why Use COUNTIF Instead of VLOOKUP?
When you just want to determine if a value exists in a list then I recommend using COUNTIF over VLOOKUP. It has a few advantages that make it more efficient, and also give you more insight to your data.
Here are 3 reasons to use COUNTIF instead of VLOOKUP (when you just want to see if a value exists in a range of cells):
- The COUNTIF function only has two arguments making it really fast and easy to write the formula. VLOOKUP has four arguments.
- COUNTIF returns the total number of matching values in the range, so you can see if there is more than one matching value. VLOOKUP cannot do this, it only returns the first match.
- If the value does not exist, COUNTIF will return a zero (0). You do not need to worry about a formula error. With VLOOKUP, the formula would return an error and you would use and error handling function like IFERROR to handle the error.
Here is a comparison table of the list above, just in case your boss asks why… 🙂
Checkout my article on VLOOKUP Explained at Starbucks if you want to learn more about how the function works.
COUNTIF vs VLOOKUP for Determining If A Value Exists
Here is an example of solving the same problem with COUNTIF vs VLOOKUP.
The COUNTIF returns a number greater than or equal to 1 if the value exists in the list. It returns a zero if the value does not exist.
The VLOOKUP formula is going to return the matching value from the list. VLOOKUP returns a #N/A error if it can't find the value in the list.
Obviously the VLOOKUP formula is longer, more complex, and will take more time to write.
Don’t Forget About COUNTIFS
The COUNTIF function has a big brother named COUNTIFS. This function allows you to specify multiple criteria in multiple ranges. You could use it to answer the question of how many Bob's are in the East region from the data set below.
=COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2, …)
You can add up to 127 criteria!
You can also use COUNTIFS with only one criteria, just like you do with COUNTIF.
That means you could use COUNTIFS all the time instead of COUNTIF. I know a lot of people do this and that is perfectly fine. I wanted to keep it simple by explaining COUNTIF first.
So now you know two new functions that you can use instead of VLOOKUP to find a matching value!
Please leave a comment below with any questions.
Get More Training
This video and lesson are from The Ultimate Lookup Formulas Course. There are over 100 short videos just like the one above included in this self-paced online course.
This course has been designed to help you master Excel's most important functions and formulas in an easy step-by-step manner.
The Ultimate Lookup Formulas Course is now part of our comprehensive Elevate Excel Training Program.
Click Here to Learn More About the Elevate Excel
Author: Oscar Cronquist Article last updated on August 23, 2022
The VLOOKUP function lets you search the leftmost column for a value and return another value on the same row in a column you specify.
Use the new XLOOKUP function which is an improved version of the VLOOKUP function available for Excel 365 users.
VLOOKUP stands for vertical lookup and your data (table_array) must be organized into records, a record on each row. To search records excel must look vertically in the data array. HLOOKUP stands for horizontal lookup but that is for another post.
Formula in cell C3:
=VLOOKUP(B3, B6:E18, 4, FALSE)
The VLOOKUP function uses lookup value AA-1611 in cell B3 and searches Table1 (cell range B6:E18) in the leftmost column. A matching value is found on row 16.
The third argument is which column you want to fetch the corresponding value from. In this example, column 4 is entered and £30.00 is returned in cell C3.
The fourth and last argument lets you choose between approximate and exact match, in this case, FALSE meaning EXACT match.
Excel function syntax
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Arguments
| lookup_value | Value you want to lookup. |
| table_array | The range you want to use, remember that the VLOOKUP function always looks in the leftmost column in your specified range. |
| col_index_num | The column number which contains the return value. |
| [range_lookup] | True or False (boolean value). True - approximate match, the leftmost column must be sorted ascending. False - Exact match. |
1. VLOOKUP - approximate match
The fourth argument [range_lookup] allows you to choose if you want an APPROXIMATE or EXACT match.
You want, in most cases, to use the EXACT match however the default value is an APPROXIMATE match. So make sure you know what you are doing.
True - Approximate match (default)
False - Exact match
This means that you get the approximate match if you don't specify the fourth argument at all.
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
The example above shows you the difference between EXACT and APPROXIMATE match. You must have your leftmost column sorted in an ascending order or you may get incorrect results.
The approximate match finds the largest value that is less than or equal to the lookup_value.
The approximate match is useful if you want to find which group or range your value belongs to. The example above shows that value 100 is the largest value that is less than or equal to 158, the corresponding value to 100 in column C is 15.
Formula in cell C3:
=VLOOKUP(E3, B3:C9, 2, TRUE)
Example weights and result amounts:
| Weight | Amount |
| 0 | 10 |
| 50 | 10 |
| 99 | 10 |
| 100 | 15 |
| 101 | 15 |
| 150 | 15 |
| 199 | 15 |
| 200 | 25 |
| 201 | 25 |
Read more: Use VLOOKUP to calculate discount percentages
Back to top
A Pivot Table can't work with related tables (power pivots do) however the VLOOKUP function can quickly merge related tables so you then can analyze data in a Pivot Table.
These two tables are related meaning they share a column (invoice), you can use the following formula to merge these two data sets based on the invoice column, this will organize data and put corresponding data next to the appropriate invoice number.
The image above shows both data sets and the VLOOKUP function in cell E3.
Formula in cell E3:
=VLOOKUP($B3, B18:D30, COLUMN(B2), FALSE)
Copy formula in cell E3 and paste to E3:F15.
Explaining formula in cell E3
Step 1 - Calculate column number to get values from
The COLUMN function returns a number representing the position of a column counting from left to right based on a cell reference.
We can use this to create a dynamic number that changes when we copy the formula and paste to adjacent cells.
The technique that makes this possible is a relative cell reference, in this case, cell reference B2.
COLUMN(B2) returns 2.
Step 2 - Change cell reference to a relative cell reference
We want to use values only from column B, use the dollar sign to lock the cell reference to column B.
This keeps the cell reference pointing to column B, however, the row part of the cell reference is relative meaning it will change when the formula is copied and pasted to other cells.
$B3
Step 3 - Get value
VLOOKUP($B3, B18:D30, COLUMN(B2), FALSE)
becomes
VLOOKUP(109, {147, "EE", "East";118, "FF", "East";108, "DD", "North";149, "CC", "North";105, "JJ", "South";117, "BB", "East";114, "HH", "South";109, "AA", "East";112, "KK", "South";134, "II", "West";124, "MM", "East";111, "GG", "West";123, "LL", "North"}, {2}, FALSE)
and returns "AA"
Back to top
3. How to use the VLOOKUP function with a cell reference specified in a cell?
This example shows you how to use the VLOOKUP function with multiple tables, it searches an Excel Table based on the specified table name in cell C3. The image above shows two Excel tables, table10 and table20.
Here is how it works, the lookup value is in B3, the Excel Table name is in C3, the column number in cell D3, and the formula is in cell E3.
The formula in cell E3 uses the values in B3, C3, and D3 to extract the appropriate value, this example shows how to use cell values instead of hardcoded values as arguments.
Formula in cell E3:
=VLOOKUP(B3,INDIRECT(C3),D3,FALSE)
The INDIRECT function allows you to convert a string to a valid cell reference.
INDIRECT(C3)
becomes
INDIRECT("table10")
and returns table10.
Step 2 - Get value
VLOOKUP(B3, INDIRECT(C3), D3, FALSE)
becomes
VLOOKUP("AA-1611", table10, 3, FALSE)
and returns "Green" in cell E3.
The INDIRECT function lets you use a cell value as the table_array argument, this lets you quickly change the value in cell C3 and search multiple tables.
4. What if the lookup value is not in the leftmost column?
The disadvantage with the VLOOKUP function is that it can only look for a value in the leftmost column. If you don't have your values in the leftmost column you have three options, rearrange your table, use the XLOOKUP function (Excel 365), or use another method demonstrated below.
The INDEX and MATCH function allows you to search any column in a table and return a value in any column on the same row. This formula is so versatile that I actually prefer INDEX + MATCH over the VLOOKUP function.
Formula in cell C3:
=INDEX(G6:G18, MATCH(D3, E6:E18, 0))
The MATCH function lets you look for a value in a column and return the relative position of the found value in the array.
MATCH(D3, E6:E18, 0)
becomes
MATCH("AA-1611", E6:E18, 0)
and returns 11. AA-1611 is found on row 11 in cell range E6:E18.
Step 2 - Get value
The INDEX function returns a value from a cell range, you specify which value based on a row and column number.
INDEX(array, [row_num], [column_num])
INDEX(G6:G18, MATCH(D3, E6:E18, 0))
becomes
INDEX(G6:G18, 11)
and returns 30 in cell C3.
Read more: How to return multiple values using VLOOKUP
Back to top
5. How to handle VLOOKUP errors
The IFERROR function allows you to return a value if the VLOOKUP function returns an error, in this case NOT FOUND!
=IFERROR(VLOOKUP(B3, Table116, 4, FALSE), "NOT FOUND!")
Back to top
6. How to use VLOOKUP in VBA?
The following VBA code demonstrates how to use VLOOKUP in a macro.
The macro searches B6:B18 using the value in cell C2 and displays the result in a message box.
VBA code Sub VLP() MsgBox Application.WorksheetFunction.VLookup(Range("C2"), Range("B6:E18"), 4, False) End SubBack to top
Get Excel *.xlsm file
VLOOKUP function.xlsm
The following 4 articles contain the VLOOKUP function.
The following 9 articles contain the VLOOKUP function.
The VLOOKUP function is one of many functions in the 'Excel' category.
Converts negative numbers to positive numbers.
Calculates the accrued interest for a security that pays periodic interest.
Calculates the accrued interest for a security that pays interest at maturity.
Calculates the arccosine, or inverse cosine, of a number.
Calculates the inverse hyperbolic cosine of a number.
Calculates the inverse cotangent of a number.
Calculates the inverse hyperbolic cotangent of a number.
Returns the address of a specific cell, you need to provide a row and column number.
Perform different specific functions to a list or database.
Calculates the depreciation for each accounting period. This function is designed for the French accounting system.
Calculates the depreciation for each accounting period.
Perform a logical test in each argument and if all arguments return TRUE the AND function returns TRUE.
Returns the number of cell ranges and single cells in a reference.
Converts full-width (double-byte) characters to half-width (single-byte) characters.
Calculates the arcsine of a number.
Calculates the inverse hyperbolic sine of a number.
Calculates the arctangent of a number.
Calculates the arctangent of an angle using specific x- and y-coordinates.
Calculates the inverse hyperbolic tangent of a number.
Calculates the average of the absolute deviations of data points from their mean.
Calculates the average of numbers in a cell range.
Returns the average of a group of values. Text and boolean value FALSE evaluates to 0. TRUE to 1.
Returns the average of cell values that are valid for a given condition.
Returns the average of cell values that evaluates to TRUE for multiple criteria.
Converts a number into a text representation with a given radix (base).
Calculates the beta distribution.
Calculates the inverse of the cumulative beta distribution.
Calculates the beta distribution, it represents outcomes in the form of probabilities.
Calculates the inverse of the beta distribution.
Converts a binary number to the decimal number system.
Converts a binary number to hexadecimal.
Converts a binary number to octal.
Calculates the individual term binomial distribution probability.
Calculates the minimum value for which the binomial distribution is equal to or greater than a given threshold value.
Calculates the individual term binomial distribution probability
Calculates a bitwise 'AND' of two numbers.
Calculates a number whose binary representation is shifted left by a specified number of bits.
Performs a bitwise 'OR' of two numbers.
Calculates the number where the binary equivalent is shifted right by a specified number of bits and then converted back to a number.
Calculates a decimal number that is a result of a bitwise comparison "XOR" of two numbers.
Rounds a number up to its nearest multiple.
Gets information about the formatting, location, or the contents of a cell.
Converts a number to the corresponding ANSI character determined by your computers character set.
Calculates the probability of the chi-squared distribution.
Calculates the inverse probability of the chi-squared distribution.
Calculates the probability of the chi-squared distribution, cumulative distribution or probability density.
Calculates the right-tailed probability of the chi-squared distribution.
Calculates the inverse of the left-tailed probability of the chi-squared distribution.
Calculates the inverse of the right-tailed probability of the chi-squared distribution.
Calculates the test for independence, the value returned from the chi-squared statistical distribution and the correct degrees of freedom. Use this function to check if hypothesized results are valid.
Calculates the test for independence, the value returned from the chi-squared statistical distribution and the correct degrees of freedom. Use this function to check if hypothesized results are valid.
Gets a value based on a number.
Returns given columns from a cell range or array.
Returns given rows from a cell range or array.
Deletes the first 32 nonprinting characters in 7-bit ASCII code in your argument.
Returns the corresponding number for the first character based on your computers character set. (PC- ANSI)
Returns the column number of the top-left cell of a cell reference.
Calculates the number of columns in a cell range.
Returns the number of combinations for a specific number of elements out of a larger group.
Calculates the number of combinations for a given number of elements from a larger group of elements.
Returns a complex number based on a real and imaginary number.
Concatenates values from multiple cells.
Joins text strings.
Calculates the confidence interval for a population mean, using a normal distribution.
Calculates the confidence interval for a population mean.
Calculates the confidence range for a population mean using a Student's t distribution.
Converts a number from one measurement system to another.
Calculates the correlation between two groups of numbers.
Calculates the cosine of an angle.
Calculates the hyperbolic cosine of a number.
Calculates the cotangent of an angle specified in radians.
Calculates the hyperbolic cotangent of a hyperbolic angle.
Counts all numerical values in an argument.
Counts the non-empty or non-blank cells in a cell range.
Counts empty or blank cells in a range.
Calculates the number of cells that is equal to a condition.
Calculates the number of cells across multiple ranges that equals all given conditions.
Calculates the covariance meaning the average of the products of deviations for each pair in two different datasets.
Calculates the covariance meaning the average of the products of deviations for each pair in two different datasets.
Calculates the sample covariance meaning the average of the products of deviations for each pair in two different datasets.
Calculates the minimum value for which the binomial distribution is equal to or greater than a given threshold value.
Calculates the cosecant of an angle (radians).
Calculates the accumulated interest based on a start and end period on a loan.
Calculates the accumulated principal based on a start and end period on a loan.
Returns a number that acts as a date in the Excel environment.
Returns the number of days, months, or years between two dates. The DATEDIF function exists in order to ensure compatibility with Louts 1-2-3.
Returns an Excel date value (serial number) based on a date stored as text.
Calculates an average based on values in a list or database that meet specific conditions.
Extracts the day as a number from an Excel date.
Calculates the number of days between two dates.
Calculates the depreciation of an asset for a given period using the fixed-declining balance method.
Counts cells containing numbers and that meet a condition or criteria.
Counts nonempty cells in a column you specify, in a database where records also meet a condition or criteria.
Calculates the depreciation of an asset for a given period using the double-declining balance method or based on user input.
Converts a decimal number to a binary number.
Converts a decimal number to a hexadecimal number.
Converts a decimal number to an octal number.
Converts a text representation of a number in a given base into a decimal number.
Calculates degrees from radians.
Evaluates whether two numerical values are equal.
Fetches a value from a column in a database whose records meet a condition or criteria.
Extracts the maximum number from a column in a database whose records match a condition or criteria.
Extracts the smallest number from a column in a database whose records match a condition or criteria.
Converts a decimal number to its equivalent in fractional numbers, used in securities denominated in dollars.
Multiplies numbers that match a condition or criteria in a database.
Removes a given number of rows or columns from a 2D cell range or array.
Calculates an estimation of the standard deviation based on a sample of a population. The function also allows you to specify criteria applied to a database.
Calculates the standard deviation based on a population. The function also allows you to specify criteria applied to a database.
Adds numbers in a database/list that meets a condition or criteria.
Returns the variance of an entire population. The numbers are in a column of records in a dataset or database that meets a given condition or criteria.
Returns a date determined by a start date and a number representing how many months.
Calculates the effective annual interest rate, given the nominal annual interest rate and the number of compounding periods per year.
Returns a URL-encoded string.
Returns an Excel date for the last day of a given month using a number and a start date.
Returns a number that represents one of the error values in Excel.
Rounds a number up to the nearest even whole number.
Checks if two values are precisely the same, it returns TRUE or FALSE. The EXACT function also considers upper case and lower case letters.
Returns e raised to the power of a number, e equals 2.71828182845904.
Increases a cell range or array by a specified number of columns and rows.
Calculates the exponential distribution representing an outcome in the form of probability.
Calculates the F probability for two tests.
Calculates the right-tailed F probability for two tests.
Calculates the two-tailed probability from an F-test
Returns the factorial of a number.
Returns the double factorial of a number.
Returns the logical (boolean) value FALSE.
Calculates the F probability of the right-tailed distribution for two tests.
Extracts values/rows based on a condition or criteria.
Extracts specific values from XML content by using the given xpath.
Returns the position of a specific string in another string, reading left to right. Note, the FIND function is case-sensitive.
Rounds a number to the specified number of decimals, formats the number in decimal format using a period and commas, and returns the result as text.
Rounds a number down, toward zero, to the nearest multiple of significance.
Rounds a number down to the nearest integer or to the nearest multiple of significance.
Rounds a number down to the nearest integer or nearest multiple of significance.
Calculates a value based on existing x and y values using linear regression. Use this function to predict linear trends.
Calculates a value based on existing x and y values using linear regression.
Returns a formula as a text string.
Calculates how often values occur within a range of values and then returns a vertical array of numbers.
Calculates the value from an F-test. The value shows if the variances from two data sets are not significantly different.
Returns the future value of an investment based on a constant interest rate.
Calculates the GAMMA value.
Calculates the gamma often used in queuing analysis (probability statistics) that may have a skewed distribution.
Calculates the gamma often used in queuing analysis (probability statistics).
Calculates the greatest common divisor that divides all given arguments without a remainder.
Calculates the geometric mean.
Returns estimated exponential growth based on given data.
Converts a hexadecimal number to a binary number.
Converts a hexadecimal number to a decimal number.
Searches the top row in a data range for a value and return another value on the same column in a row you specify.
Returns an integer representing the hour of an Excel time value.
Combines cell ranges or arrays. Joins data to the first blank cell to the right of a cell range or array (horizontal stacking)
Builds a link in a cell.
Returns one value if the logical test is TRUE and another value if the logical test is FALSE.
If the value argument returns an error, the value_if_error argument is used. If the value argument does NOT return an error, the IFERROR function returns the value argument.
Handles #N/A errors only, it returns a specific value if the formula returns a #N/A error.
Checks whether one or more conditions are met and returns a value that corresponds to the first TRUE condition.
Calculates the absolute value (modulus) of a complex number in x + yi or x + yj text format.
Inserts an image into cells.
Calculates the imaginary value of a complex number in x + yi or x + yj text format.
Calculates theta θ which is an angle displayed in radians based on complex numbers in rectangular form.
Calculates the complex conjugate of a complex number in x + yi or x + yj text format.
Returns a value or reference from a cell range or array, you specify which value based on a row and column number.
Returns the cell reference based on a text string and shows the content of that cell reference.
Returns information about the current operating environment, file path, number of active worksheets, Excel version etc.
Removes the decimal part from positive numbers and returns the whole number (integer) except negative values are rounded down to the nearest integer.
Returns a value representing the y-value where a line intersects the y-axis.
Calculates the interest payment for a specific period for an investment based on repeated constant payments and a constant interest rate.
Returns TRUE if the argument is an empty cell, returns FALSE if not.
Returns TRUE if a cell returns an error, except error value #N/A.
Returns TRUE if a cell contains an error.
Returns TRUE if a cell contains a formula, FALSE if text, number or boolean value.
Returns TRUE if value is boolean. A boolean value is either TRUE or FALSE.
Returns TRUE if value is a #N/A error.
Returns TRUE if value is not text, also returns TRUE if cell is empty.
Checks if a value is a number, returns TRUE or FALSE.
Returns TRUE if a cell contains an odd number, FALSE if even number.
Calculates the interest paid during a specific period of an investment.
Returns TRUE if value refers to a reference.
Returns TRUE if argument is text.
Build custom functions without VBA, macros or javascript.
Calculates the k-th largest value from an array of numbers.
Calculates the least common multiple. The least common multiple is the smallest positive integer that is a multiple of all integer arguments. Use the LCM function to find fractions with different denominators.
Extracts a specific number of characters always starting from the left.
Returns the number of characters in a cell value.
Lets you name intermediate calculation results which can shorten formulas considerably and improve performance.
Returns an array of values representing the parameters of a straight line based on the "least squares" method.
Calculates the natural logarithm of a number. Natural logarithms are based on the constant e.
Calculates the logarithm of a number to a specific base.
Calculates the logarithm of a number using the base 10.
Returns an array of values representing the parameters of an exponential curve that fits your data, based on the "least squares" method.
Calculates the lognormal distribution of argument x, based on a normally distributed ln(x) with the arguments of mean and standard_dev.
Calculates the cumulative lognormal distribution of argument x, based on a normally distributed ln(x) with the arguments of mean and standard_dev.
Find a value in a cell range and return a corresponding value on the same row.
Converts a value to lower case letters.
Returns the relative position of an item in an array that matches a specified value in a specific order.
Calculate the largest number in a cell range.
Calculates the highest value based on a condition or criteria.
Calculates the median based on a group of numbers. The median is the middle number of a group of numbers.
Returns a substring from a string based on the starting position and the number of characters you want to extract.
Returns the smallest number in a cell range.
Returns the smallest number. Text values and blanks are ignored, boolean value TRUE evaluates to 1 and FALSE to 0 (zero).
Calculates the smallest value based on a given set of criteria.
Returns a whole number representing the minute based on an Excel time value. The returned number is ranging from 0 to 59.
Calculates the inverse matrix for a given array.
Calculates the matrix product of two arrays, an array as the same number of rows as array1 and columns as array2.
Returns the remainder after a number is divided by divisor.
Calculates the most frequent number in a cell range.
Returns the most frequent number in a cell range. It will return multiple numbers if they are equally frequent.
Calculates the most frequent value in an array or range of data.
Extracts the month as a number from an Excel date.
Calculates a number rounded to a given multiple.
Calculates the ratio of the factorial of a sum of values to the product of factorials.
Calculates the identity matrix for a given dimension
Returns a value converted into a number.
Returns the error value #N/A meaning "value is not available".
Returns the number of working days between two dates, excluding weekends. It also allows you to ignore a list of holiday dates that you can specify.
NETWORKDAYS.INTL function
Calculates the number of working days between two dates, excluding weekends.
Calculates the nominal annual interest rate based on the effective rate and the number of compounding periods per year.
Calculates the normal distribution for a given mean and standard deviation.
Calculates the inverse of the normal cumulative distribution for a given mean and standard deviation.
Returns the boolean opposite to the given argument.
Returns the current date and time.
Calculates the number of periods for an investment based on periodic, constant payments and a fixed interest rate.
Returns a reference to a range that is a given number of rows and columns from a given reference.
Evaluates a logical expression in each argument and if at least one argument returns TRUE the OR function returns TRUE. If all arguments return FALSE the OR function also returns FALSE.
Calculates how many periods required by an investment to reach a given amount based on a rate in percentage.
Calculates the percent rank of a given number compared to the whole data set.
Calculates the percent rank of a given number in a data set.
Calculates the percent rank of a given number compared to the whole data set.
Returns the number of permutations for a set of elements that can be selected from a larger number of elements.
Returns the number of permutations for a specific number of elements that can be selected from a larger group of elements.
Calculates a number of the density function for a standard normal distribution.
Returns the number pi (¶).
Returns the payment needed for borrowing a fixed sum of money based on constant payments and interest rate.
Calculates a number raised to a power.
Calculates the principal payment for a specific period for an investment based on repeated constant payments and a constant interest rate.
Calculates the price per $100 nominal value of a bond that pays interest at maturity.
Calculates the probability that values in a range are between a given lower and upper limit.
Returns the product of the numbers given in the argument.
Calculates the net present value for an investment or loan.
Returns the quartile of a data set.
Returns the quartile of a data set, based on percentile values from 0..1, inclusive.
Returns the integer portion of a division.
Converts degrees to radians.
Calculates a random real number greater than or equal to 0 and less than 1.
Creates an array of random numbers
Returns a random whole number between the numbers you specify.
Calculates the rank of a specific number compared to a list of numbers.
Returns the rank of a number out of a list of numbers.
Calculates the rank of a number in a list of numbers, based on its position if the list were sorted.
Returns the interest rate per period of an annuity.
Substitutes a part of a text string based on the number of characters and length with a text string you provide.
Repeats a specific text a chosen number of times.
Extracts a specific number of characters always starting from the right.
Rounds a number based on the number of digits you specify.
Rounds a number down based on the number of digits to which you want to round the number.
Calculates a number rounded up based on the number of digits to which you want to round the number.
Calculates the row number of a cell reference.
Calculate the number of rows in a cell range.
Calculates the growth of an investment in percent per period.
Returns the number of the character at which a specific character or text string is found reading left to right (not case-sensitive)
Calculates the secant of an angle.
Calculates the hyperbolic secant of an angle.
Returns an integer representing the second based on an Excel time value
Creates a list of sequential numbers
Calculates the sum of a power series based on a formula.
Returns the sheet number of the cell reference sheet.
Returns the number of sheets in a reference.
Returns the sign of a number. 1 for a positiv number, 0 (zero) for a 0 (zero) and -1 for a negative number.
Calculates the sine of an angle.
Calculates the hyperbolic sine of a number.
Calculates the skewness of a group of values.
Calculates the slope of the linear regression line through coordinates.
Returns the k-th smallest value from a group of numbers.
Sorts values from a cell range or array
Sorts a cell range or array based on values in a corresponding range or array.
Calculates the positive square root.
Calculates a normalized value from a distribution characterized by mean and standard_dev.
Calculates the standard deviation of a group of values.
Returns standard deviation based on the entire population.
Returns standard deviation based on a sample of the entire population.
Estimates the standard deviation from a sample of values.
Returns the standard deviation based on the entire population, including text and logical values.
Downloads stock prices based on a stock quote
Replaces a specific text string in a value. Case sensitive.
Returns a subtotal from a list or database, you can choose from a variety of arguments that determine what you want the function to do.
Allows you to add numerical values, the function returns the sum in the cell it is entered in. The SUM function is cleverly designed to ignore text and boolean values, adding only numbers.
Sums numerical values based on a condition.
Adds numbers based on criteria.
Calculates the product of corresponding values and then returns the sum of each multiplication.
Calculates the sum of the squares of the arguments.
Calculates the sum of the difference of squares of corresponding values in two arrays.
Calculates the sum of the sum of squares of corresponding values in two arrays.
Calculates the sum of squares of differences of corresponding values in two arrays.
Returns a given value determined by an expression and a list of values.
Calculates the yearly asset depreciation of a given year.
Returns a text value if the argument is a text value.
Returns a given number of rows or columns from a 2D cell range or array.
Calculates the tangent of an angle.
Calculates the hyperbolic tangent of a number.
Calculates the par amount (face value) for a Treasury bill.
Calculates the yield for a Treasury bill.
Converts a value to text in a specific number format.
Extracts a string after a specific substring in a given value.
Extracts a string before a specific substring from a given value.
Combines text strings from multiple cell ranges.
Splits a string into an array based on delimiting values.
Returns a decimal value between 0 (zero) representing 12:00:00 AM and 0.99988426 representing 11:59:59 P.M.
Returns a decimal number based on a text string.
Rearranges values in 2D cell ranges to a single column.
Returns the Excel date (serial number) of the current date.
Rearranges values from a 2D cell range to a single row.
Converts a vertical range to a horizontal range, or vice versa.
Calculates values along a linear trend.
Deletes all blanks or space characters except single blanks between words in a cell value.
Calculates the mean of the interior of a data set.
Returns the logical (boolean) value TRUE.
Removes the fractional part of the number to an integer.
Use TYPE to find out what type of data is returned by a function or formula.
Calculates a character based on a Unicode number.
Returns a Unicode number based on a character.
Returns a unique or unique distinct list.
Converts a value to upper case letters.
Converts a text string that represents a number to a number.
Returns the variance based on the entire population. The function ignores logical and text values.
The VAR.S function tries to estimate the variance based on a sample of the population. The function ignores logical and text values.
Calculates the depreciation of an asset for a given period using the double-declining balance method or based on user input, you may use partial periods in this function.
Lets you search the leftmost column for a value and return another value on the same row in a column you specify.
Combines cell ranges or arrays. Joins data to the first blank cell at the bottom of a cell range or array (vertical stacking)
Gets data from a web service on the Internet or Intranet.
Converts a date to a weekday number from 1 to 7.
Calculates a given date's week number based on a return_type parameter that determines which day the week begins.
Returns a date based on a start date and a given number of working days (nonweekend and nonholidays).
Rearranges values from a single row to a 2D cell range based on a given number of values per column.
Rearranges values from a single row to a 2D cell range based on a given number of values per column.
Search one column for a given value, and return a corresponding value in another column from the same row.
Searches for an item in an array or cell range and returns the relative position.
Calculates net present value for cash flows that may or may not be periodic
Calculates the logical exclusive OR meaning if at least one of the arguments evaluates to TRUE then the XOR returns TRUE. All arguments must be evaluated to FALSE for the XOR function to return FALSE.
Converts a date to a number representing the year in the date.
Returns the fraction of the year based on the number of whole days between a start date and an end date.
Calculates the yield for a security that pays interest. The YIELD function is designed to calculate the bond yield.
Excel functions that let you resize, combine, and shape arrays.
Functions for backward compatibility with earlier Excel versions. Compatibility functions are replaced with newer functions with improved accuracy. Use the new functions if compatibility isn't required.
Perform basic operations to a database-like structure.
Functions that let you perform calculations to Excel date and time values.
Let's you manipulate binary numbers, convert values between different numeral systems, and calculate imaginary numbers.
Calculate present value, interest, accumulated interest, principal, accumulated principal, depreciation, payment, price, growth, yield for securities, and other financial calculations.
Functions that let you get information from a cell, formatting, formula, worksheet, workbook, filepath, and other entitites.
Functions that let you return and manipulate logical values, and also control formula calculations based on logical expressions.
These functions let you sort, lookup, get external data like stock quotes, filter values based a condition or criteria, and get the relative position of a given value in a specific cell range. They also let you calculate row, column, and other properties of cell references.
You will find functions in this category that calculates random values, round numerical values, create sequential numbers, trigonometry, and more.
Calculate distributions, binomial distributions, exponential distribution, probabilities, variance, covariance, confidence interval, frequency, geometric mean, standard deviation, average, median, and other statistical metrics.
Functions that let you manipulate text values, substitute strings, find string in value, extract a substring in a string, convert characters to ANSI code among other functions.
Get data from the internet, extract data from an XML string and more.