How to Use Excel Formulas to Fetch the Last Month’s Value

To fetch the last month’s value using excel formula, you need to use a combination of functions that can compare the dates and return the corresponding values. There are different ways to achieve this, but the main idea is to use a lookup function that can match the date criteria and return the value from another column.

One possible lookup function is the LOOKUP function, which can perform an approximate match on a sorted array of values. The LOOKUP function has the following syntax:

LOOKUP(lookup_value, lookup_array, result_array)

where:

• lookup_value is the value to search for in the first array.
• lookup_array is the array of values to search in, sorted in ascending order.
• result_array is the array of values to return from, corresponding to the lookup_array.

Another possible lookup function is the XLOOKUP function, which can perform an exact match on an unsorted array of values. The XLOOKUP function has the following syntax:

XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

where:

• lookup_value is the value to search for in the lookup_array.
• lookup_array is the array of values to search in.
• return_array is the array of values to return from, corresponding to the lookup_array.
• [if_not_found] is an optional argument that specifies what to return if the lookup_value is not found. The default is #N/A.
• [match_mode] is an optional argument that specifies the match type. The default is 0 for exact match. Other options are -1 for exact match or next smaller, 1 for exact match or next larger, and 2 for wildcard match.
• [search_mode] is an optional argument that specifies the search mode. The default is 1 for search from first to last. Other options are -1 for search from last to first, 2 for binary search on ascending values, and -2 for binary search on descending values.

To compare the dates, you need to use a function that can convert the dates to a common format, such as the TEXT function. The TEXT function has the following syntax:

TEXT(value, format_text)

where:

• value is the value to format.
• format_text is the format code to apply to the value.

For example, you can use the format code "mmyy" to convert the dates to a string of month and year, such as "0124" for January 2024.

Procedures

To fetch the last month’s value using excel formula, you can follow these steps:

1. Prepare your data in a table with columns for date, product, and value. For example:

2. Decide which lookup function you want to use. If your data is sorted by date, you can use the LOOKUP function. If your data is not sorted or you want more flexibility, you can use the XLOOKUP function.
3. Enter the formula in a new column, using the TEXT function to compare the dates. For example, if you want to fetch the value for product A in the previous month, you can use one of these formulas:

• Using LOOKUP:

=LOOKUP(2,1/(TEXT($A$2:$A$7,"mmyy")=TEXT(EDATE(A2,-1),"mmyy"))*($B$2:$B$7="A"),$C$2:$C$7)

• Using XLOOKUP:

=XLOOKUP(TEXT(EDATE(A2,-1),"mmyy")&"A",TEXT($A$2:$A$7,"mmyy")&$B$2:$B$7,$C$2:$C$7)

4. Copy the formula down to fill the column. You should see the values for the previous month, or an error if there is no match. For example:

Comprehensive explanation

The formulas work by using the TEXT function to convert the dates to a common format, such as "mmyy", and then comparing them with the date criteria. The date criteria is the previous month of the current date, which can be obtained by using the EDATE function with a negative number of months. The EDATE function has the following syntax:

EDATE(start_date, months)

where:

• start_date is the date to start from.
• months is the number of months to add or subtract from the start_date.

For example, EDATE(A2,-1) returns the date of the previous month of the date in cell A2.

The formulas also use the product criteria, which is the product name in the same row. The product criteria is concatenated with the date criteria using the & operator, to form a unique lookup value. For example, TEXT(EDATE(A2,-1),"mmyy")&"A" returns the lookup value "0124A" for product A in January 2024.

The formulas then use the lookup function to search for the lookup value in the lookup array, which is composed of the date and product columns formatted with the TEXT function. For example, TEXT($A$2:$A$7,"mmyy")&$B$2:$B$7 returns the lookup array {"0124A";"0124B";"0124C";"0224A";"0224B";"0224C"}.

The formulas then return the value from the result array, which is the value column. For example, $C$2:$C$7 returns the result array {10;20;30;20;10;10}.

The formulas use different methods to handle the cases where there is no match. The LOOKUP function uses the 2 as the lookup value, which is larger than any value in the lookup array, and the 1/ as the divisor, which converts the TRUE and FALSE values to 1 and #DIV/0! errors. This way, the LOOKUP function will always return the last match or an error if there is no match. The XLOOKUP function uses the [if_not_found] argument to specify what to return if there is no match, which can be any value or error. The default is #N/A.

Scenario

To illustrate the formulas with a detailed example, let’s use the following scenario:

• You have a table of sales data for different products in different months, as shown below:

• You want to fetch the sales for product B in the previous month, for each month in the table.

To fetch the sales for product B in the previous month, you can use one of these formulas in a new column:

• Using LOOKUP:

=LOOKUP(2,1/(TEXT($A$2:$A$10,"mmyy")=TEXT(EDATE(A2,-1),"mmyy"))*($B$2:$B$10="B"),$C$2:$C$10)

• Using XLOOKUP:

=XLOOKUP(TEXT(EDATE(A2,-1),"mmyy")&"B",TEXT($A$2:$A$10,"mmyy")&$B$2:$B$10,$C$2:$C$10)

The formulas will return the following results:

Scenario (continued)

• Using XLOOKUP:

=XLOOKUP(TEXT(EDATE(A2,-1),"mmyy")&"B",TEXT($A$2:$A$10,"mmyy")&$B$2:$B$10,$C$2:$C$10)

The formulas will return the following results:

The formulas work as explained in the previous section, by using the TEXT function to convert the dates and products to a common format, and then using the lookup function to search for the previous month’s value in the lookup array and return it from the result array.

Other approaches

There are other ways to fetch the last month’s value using excel formula, such as using the SUMIFS function or the INDEX and MATCH functions. Here are some examples of how to use these functions:

• Using SUMIFS:

=SUMIFS($C$2:$C$10,$A$2:$A$10,EDATE(A2,-1),$B$2:$B$10,B2)

The SUMIFS function has the following syntax:

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

where:

• sum_range is the range of cells to sum.
• criteria_range1 is the first range of cells to evaluate with the criteria1.
• criteria1 is the first condition to apply to the criteria_range1.
• [criteria_range2, criteria2] are optional additional ranges and conditions to apply.

The SUMIFS function sums the values in the sum_range that meet all the criteria specified. In this case, the criteria are the previous month of the current date, and the same product as the current row.

• Using INDEX and MATCH:

=INDEX($C$2:$C$10,MATCH(TEXT(EDATE(A2,-1),"mmyy")&B2,TEXT($A$2:$A$10,"mmyy")&$B$2:$B$10,0))

The INDEX function has the following syntax:

INDEX(array, row_num, [column_num])

where:

• array is the range of cells or array constant to return a value from.
• row_num is the row position in the array to return a value from.
• [column_num] is the optional column position in the array to return a value from.

The INDEX function returns the value at the intersection of the row_num and column_num in the array. If the column_num is omitted, the function returns the value in the row_num of the array.

The MATCH function has the following syntax:

MATCH(lookup_value, lookup_array, [match_type])

where:

• lookup_value is the value to search for in the lookup_array.
• lookup_array is the range of cells or array constant to search in.
• [match_type] is the optional number that specifies the match type. The default is 1 for less than or equal to. Other options are 0 for exact match, and -1 for greater than or equal to.

The MATCH function returns the relative position of the lookup_value in the lookup_array. If the match_type is 0, the function returns the exact match or an error if there is no match.

The INDEX and MATCH functions work together to return the value in the result_array that matches the lookup_value in the lookup_array. In this case, the lookup_value is the previous month and product of the current row, and the lookup_array and result_array are the same as in the previous formulas. The match_type is 0 for exact match.