I have an example spreadsheet that contains the following columns: Location, Month, Clients, Previous Month, and Previous Month's Clients.

I need a formula that will fill the Previous Month's Clients column with the previous month's number of clients for that location. The columns are unsorted, as the information comes through when the location owners fill out a form linked to the spreadsheet.

The formula also needs to be able to handle blank cells in the Clients column, as sometimes the location owners don't provide that information.

The attempt that I've made doesn't work for the first two months, but for some reason works for the third month (as shown in the example sheet):

=ARRAYFORMULA(IFNA(VLOOKUP(A3:A, FILTER(A3:D, MATCH(DATEVALUE(D3:D), DATEVALUE(B3:B), 0)), 3, FALSE)))

Hopefully I've explained that somewhat clearly. Let me know if I can clarify anything!

CodePudding user response：

I've added a new sheet ("Erik Help") to your sample spreadsheet.

You don't need the column that listed the previous month.

However, your data was not normalized at all. That is, your Col-B dates, while they look normalized, are actually all different dates within each month/year; you just formatted them to look similar. Then, you were trying to use that "previous month" column, which was text; and text can't compare with dates at all.

Here is the formula I shared in my sheet, cell D1 (after deleting your first row and moving the headers to the new Row 1):

=ArrayFormula({"Previous Month's Clients"; IF(A2:A="",,IFERROR(VLOOKUP(A2:A&EOMONTH(B2:B,-2) 1,{A2:A&EOMONTH(B2:B,-1) 1,C2:C},2,FALSE)))})

This formula will produce the header (which you can change within the formula itself as desired) and all results.

I used the EOMONTH function to normalize "this month" and "last month" dates to all fall on the first day of "this month" or "last month." Then, by concatenating the location with those normalized dates, VLOOKUP can look for an exact string. Where none is found, IFERROR returns null.