I am going crazy trying to do a wild card search within a date range.

[MechaMacster]

Context:
I'm using excel as my budgeting tool. Right now I'm exporting all transactions from my bank and dropping them into a sheet, then I have a series of vlookups looking for specific bills to see if they've been paid. These vlookups use a wildcard search since the name on the transaction usually contains number strings. In addition, I have it making sure the associated date of the transaction is relatively close to the due date I have marked. This is my formula: =IFNA(IF(AND((VLOOKUP("*"&C2&"*",'[Bank Transactions Database.xlsx]Sheet1'!$A:$B,2,FALSE)>=D2-10),(VLOOKUP("*"&C2&"*",'[Bank Transactions Database.xlsx]Sheet1'!$A:$B,2,FALSE)<=D2+10)),"Y",""),"")

This works great, until a new month comes along and old transactions lose their flag showing they were paid. I know vlookup will only find the first match (I'm sorted newest to oldest on that transaction list), but I can't for the life of me figure out how I can use the combination of the bill name with the due date to check for payment on older bills. I've tried XLookup with multiple criteria, but it only uses an exact match. Any tries of with making the date a range results in #Spill! errors or the likes. Any advice?

Sheet where I would like this flagging formula:

Description	Entry Appearance	Due Date	Amount	Confirmed?
Bill Name	word that is unique and appears in transaction	approximate due date	not always accurate	This is where I want the flag

Sheet where the transactions are:

Description	Date	Amount
Transaction name usually with a specific word along with a bunch of other values	date the payment went through	should be apparent

Any ideas on how to accomplish this? And is this enough info? Thanks in advance for saving me further heartache as my next steps if this hail mary doesn't work includes throwing my keyboard.


EDIT:
oh, more context: here's the closest I've gotten with XLookup:
=XLOOKUP(("*"&C3&"*")&(D3-1),('[Bank Transactions Database.xlsx]Sheet1'!$A:$A)&('[Bank Transactions Database.xlsx]Sheet1'!$B:$B),'[Bank Transactions Database.xlsx]Sheet1'!$B:$B,"Not Working",2)

It is close to what I'm looking for, but still requires an exact match of sorts of the date (column D). If I ALWAYS paid the bill the day before it was due, then the above would work great, but that's not how life usually works.

 

[igold]

Hi MechaMacster, welcome to the MrExcel Forum-

I too (as I am sure many others as well), use Excel as a budgeting/bank/Stock tracking tool. What I would suggest is that everything becomes easy if you store your data properly. What I have done is to create an ongoing Table which serves as my Transaction Table and everything is based off that. My columns loosely are as such:

Date - Type - Activity - Symbol - Qty - Amount - Acct - Year - Month - Received etc.

I have then created multiple Queries, Pivottables, Tables, Graphs, or whatever to retrieve the information I want to see. My table was started in 2008 and has over 10,000 rows. Each row is a unique transaction. If you create your table with every row being a unique transaction (even if it is just the date), you will be thanking yourself for years down the line.

If you like to download your info from your bank, you may want to manipulate that download to a format that fits your table. The big caveat with that, is that when your bank changes its download format (trust me they will), you are screwed. Been there, done that, too many times. It is best to bite the bullet now and come up with a routine that takes your bank download out of the equation.

It takes work and discipline, but it is a lot like an address book. When you first start filling it out, it seems like a lot of work, but two years later, when you have hundreds of people in it, you say to yourself, how did I live without this.

 

[MechaMacster]

Hi MechaMacster, welcome to the MrExcel Forum-

I too (as I am sure many others as well), use Excel as a budgeting/bank/Stock tracking tool. What I would suggest is that everything becomes easy if you store your data properly. What I have done is to create an ongoing Table which serves as my Transaction Table and everything is based off that. My columns loosely are as such:

Date - Type - Activity - Symbol - Qty - Amount - Acct - Year - Month - Received etc.

I have then created multiple Queries, Pivottables, Tables, Graphs, or whatever to retrieve the information I want to see. My table was started in 2008 and has over 10,000 rows. Each row is a unique transaction. If you create your table with every row being a unique transaction (even if it is just the date), you will be thanking yourself for years down the line.

If you like to download your info from your bank, you may want to manipulate that download to a format that fits your table. The big caveat with that, is that when your bank changes its download format (trust me they will), you are screwed. Been there, done that, too many times. It is best to bite the bullet now and come up with a routine that takes your bank download out of the equation.

It takes work and discipline, but it is a lot like an address book. When you first start filling it out, it seems like a lot of work, but two years later, when you have hundreds of people in it, you say to yourself, how did I live without this.

Thank you for the reply! So, right now I'm using just a simple macro tool to normalize my bank account so it's always consistent. When the bank changes their format, I just tweak my macro (not VBA or anything fancy, just a key recording macro program) and it's good to go until the next time the bank changes things for funsies.

Regarding your recommendation, are you saying you manually populate all of that data for your pivottables and such?

 

[igold]

Yes is the short answer. However all Pivottables (there are many) are using the one Transactions Table as the data source. In reality something like this screams for a database program such as Access (I am fluent in Access), but when I started it, I never envisioned what is has now become.
If you look up the term "Data Normalization". you can get some insight on how your table should be set up.

I do have a small routine set up that will copy previously entered rows of data and then all I do is go in and change the date/amount. For example, I pick the name of a Credit Card in a dropdown. The routine would then start at the bottom of my table and find the last time I entered that particular Credit Card bill and paste it to the bottom line of the table. I would then change the Due Date and the amount only. By doing it like this, I can insure that I maintain Data Integrity and Consistency throughout the table. This includes the stupid little things like making sure the spelling of the same items is always correct or that the Capitalization of names is always the same.

I do have another table that retrieves Stock Data and I tie that in on some reports and pivottables.
It may seem like a lot of work, but in reality it is a joy. It is also always a work in progress. I am always tweaking things.

 

[MechaMacster]

If anyone is curious, I ended up adding some manual work to my sheet, but left it mainly automated. I kept the vlookup formula above, but set conditional formatting to highlight any cells that contain a "Y" for my flag AND contains a formula. So, when I import my bank info it should highlight those that switch to paid. Then I can copy and paste values to remove the formula so they will be marked as paid permanently. I'm not sure if that's "best of both worlds" or "lesser of two evils", but it works.