Excel MATCH Function

Summary 

MATCH is an Excel function used to locate the position of a lookup value in a row, column, or table. MATCH supports approximate and exact matching, and wildcards (* ?) for partial matches. Often, the INDEX function is combined with MATCH to retrieve the value at the position returned by MATCH.

Purpose 

Get the position of an item in an array

Return value 

A number representing a position in lookup_array.

Syntax 

=MATCH (lookup_value, lookup_array, [match_type])

Arguments 

lookup_value - The value to match in lookup_array.

lookup_array - A range of cells or an array reference.

match_type - [optional] 1 = exact or next smallest (default), 0 = exact match, -1 = exact or next largest.

Usage notes 

Use the MATCH function to get the position of a value in an array. Match offers three different matching modes, which makes it more flexible than other lookup functions. When used with INDEX, MATCH can retrieve the value at the matched position.

Match type information

Match type is optional. If not provided, match type defaults to 1 (exact or next smallest). When match type is 1 or -1, it is sometimes referred to as "approximate match". However, keep in mind that MATCH will find an exact match with all match types, as noted in the table below:

Match type      Behavior

1                 Exact match or next smallest value. The lookup_array must be sorted in ascending order.

0                      Exact match only. The lookup_array does not need to be sorted.

-1                Exact match or next largest value. The lookup_array must be sorted in descending order.

Note: Be sure to set match type to zero (0) if you require an exact match. The default setting of 1 can cause MATCH to return results that look "normal" but are in fact incorrect. I recommend always providing a value for match_type, as a reminder of what behavior is expected.

Basic exact match

When match type is set to zero, MATCH performs an exact match. In the example below, the formula in E3 is:

=MATCH(E1,B2:B7,0)

Basic exact match with MATCH function

Basic approximate match

When match type is set to 1, MATCH will perform an approximate match on values sorted A-Z, finding the largest value less than or equal to the lookup value. In the example shown below, the formula in E3 is:

=MATCH(E1,B2:B7,1)

Basic approximate match with MATCH function

Basic wildcard match

When match type is set to zero (0), MATCH can perform a match using wildcards. In the example shown below, the formula in E1 is:

=MATCH(E1,B2:B7,0)

This is equivalent to:

=MATCH("pq*",B3:B11,0)

Excel IF Function

Summary 

The IF function can perform a logical test and return one value for a TRUE result, and another for a FALSE result. For example, to "pass" scores above 70: =IF(A1>70,"Pass","Fail"). More than one condition can be tested by nesting IF functions. The IF function can be combined with logical functions like AND and OR.

Purpose 

Test for a specific condition

Return value 

The values you supply for TRUE or FALSE

Syntax 

=IF (logical_test, [value_if_true], [value_if_false])

Arguments 

logical_test - A value or logical expression that can be evaluated as TRUE or FALSE.

value_if_true - [optional] The value to return when logical_test evaluates to TRUE.

value_if_false - [optional] The value to return when logical_test evaluates to FALSE.

Usage notes 

Use the IF function to test for or evaluate certain conditions, and then react differently depending on whether the test was TRUE or FALSE.

In the example shown, we want to assign either "Pass" or "Fail" based on a test score. A passing score is 70 or higher. The formula in C2, copied down, is:

=IF(B2>=70,"Pass","Fail")

Translation: If the value in B2 is greater than or equal to 70, return "Pass". Otherwise, return "Fail".

The logical flow this formula can be reversed. The formula below returns the same result:

=IF(B2<70,"Fail","Pass")

Translation: If the value in B2 is less than 70, return "Fail". Otherwise, return "Pass".

Both formulas above, when copied down, will return correct results.

Logical operators

When you are constructing a test with IF, you can use any of the following logical operators:

Comparison operator	Meaning	Example
=	equal to	A1=D1
>	greater than	A1>D1
>=	greater than or equal to	A1>=D1
<	less than	A1<d1< td=""></d1<>
<=	less than or equal to	A1<=D1
<>	not equal to	A1<>D1

IF with AND, OR

The IF function can be combined with the AND function and the OR function. For example, to return "OK" when A1 is between 7 and 10, you can use use a formula like this:

=IF(AND(A1>7,A1<10),"OK","")

Translation: if A1 is greater than 7 and less than 10, return "OK". Otherwise, return nothing ("").

To return B1+10 when A1 is "red" or "blue" you can use the OR function like this:

=IF(OR(A1="red",A1="blue"),B1+10,B1)

Translation: if A1 is red or blue, return B1+10, otherwise return B1.

Calculate Due Dates for Payments or Years Toward Retirement with EDATE Function

Summary 

The Excel EDATE function returns a date on the same day of the month, n months in the past or future. You can use EDATE to calculate expiration dates, maturity dates, and other due dates. Use a positive value for months to get a date in the future, and a negative value for dates in the past. 

Purpose 

Shift date n months in future or past

Return value 

New date as Excel serial number

Syntax 

=EDATE (start_date, months)

Arguments 

start_date - Start date as a valid Excel date.

months - Number of months before or after start_date.

Example

EDATE is a date and time function in excel which adds a given number of months into a date and gives us a date in a numerical format of date, the arguments this function takes is date and integers, date as the start date to begin with and number of months are integers to add it to the given start date, the output returned by this function is also a date value, the method to use this function is =Edate( Start Date, Months).