IF formula has reached it's limit - need another solution!

Hi there,

So I have a spreadsheet which holds financial transactions, and the date the transaction took place.
My company have a financial calendar which means that transactions after the monthly 'cut off' date, will fall into the following month. The cut off date is different each month. So we have a transaction date, and a posting date. The posting date just being the month and the year.

We always know when the cut off date is, so these dates are in a worksheet, and in order to know which month the transactions fall into (the posting date), we have nested IF functions to say if the transaction date is less than _ then show _ and it displays a month and year for the relevant posting date.

However.

We have 24 months we need to account for, and the IF function has worked with 19 nested IFs but for month 20 it's said there are too many arguments.

Any ideas for an alternative???

Jemma

Good afternoon heytherejem

Set up a table on another sheet, which lists all the transaction dates (ie 365 days per year - not just the used ones) in a left hand column, and the relevant posting date to the right of this column.
Then use a VLOOKUP formula to compare the transaction date and determine the relevant posting date.

HTH

DominicB

You can often replace multiple IF statements with one simple VLOOKUP function of the form:

VLOOKUP(sought_item, lookup_table, column_num, search_type)

where the sought_item parameter is the cell that contains your transaction date, the lookup_table is a list of dates and corresponding month/year, column_num will be 2 in this case, and search_type will be TRUE (or 1, or omitted). In your lookup_table, you only need to show the first date that the month/year applies to, like this:

1/01/2018 ...... 01/18
29/01/2018 .... 02/18
26/02/2018 .... 03/18

and so on. You can give the table a name, or you can use the range reference (but ensure that you use absolute addressing so that the references do not change when you copy the formula elsewhere).

Hope this helps.

Pete

Yeah but with a Vlookup we would have to list out every single date in the last two years and assign it the correct posting date. At the moment the IF formula is just seeing if the transaction date is less than the cut off date and if so, it displays one month, and if it's not less than it displays the next month.

The nested IF just points to the date, asks if it's less than - say 20th Jan 2016 - to return "01 2016". If it's less than 17th Feb 2016 to return "02 2016" and so forth for 19 months, but we can't do the last 5 months (got to be 2 years).

If we have to do a vlookup sobeit but I figured there might be an alternative...

Check out my reply - you will only need a table with 24 entries, where each entry shows only the FIRST date for each range of dates, rather than the LAST one which you seem to be using.

Hope this helps.

Pete

Hi Pete,

That looks like it would work perfectly, if only I understood how to create a vlookup to a table like that!
I have to log off now, but i'll have a look at this tomorrow using the data - and if I get stuck, i'll attach some sample data to this thread and if you're about tomorrow I would really appreciate your help :-)

I see you're Warrington-based - did you ever watch the sitcom 'Trollied' about the supermarket in Warrington? Very funny!