Hi folks,
I am stuck and am hoping you can help.
I have given up on budgeting software and am attempting my own Excel version of a budget for my household. In a worksheet, I have downloaded all of our bank activity for the year. Many of the transactions are repetitive, but the bank reference includes the vendor name plus non-recurring reference numbers within the text string that vary based on the date of the transaction (so not just a payee name in other words). I was hoping to do a vlookup of sorts that would "read" the vendor name within all the other text and return the type of expense it is. For example, on sheet 1, I have the transactions. Let's say three of them look something like the below:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Trans Date
[/TD]
[TD]Payee
[/TD]
[TD]Amount
[/TD]
[TD]Description
[/TD]
[/TR]
[TR]
[TD]01/01/16
[/TD]
[TD]REF#201601011544 CRAPPY CABLE CO CUST X251A
[/TD]
[TD]150.00
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]01/02/16
[/TD]
[TD]SUNSHINE MARKET REF 1216115QD5667
[/TD]
[TD]80.00
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]01/02/16
[/TD]
[TD]POS PUMP 4 MORRO BAY ACME FUEL STATION
[/TD]
[TD]20.00
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
On the second sheet, I have my "index" references, like this:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Vendor Name Ref
[/TD]
[TD]Exp Description
[/TD]
[/TR]
[TR]
[TD]CRAPPY CABLE
[/TD]
[TD]Internet/Home Phone
[/TD]
[/TR]
[TR]
[TD]ACME
[/TD]
[TD]Gasoline
[/TD]
[/TR]
[TR]
[TD]SUNSHINE
[/TD]
[TD]Groceries
[/TD]
[/TR]
[TR]
[TD]FRED'S GAS
[/TD]
[TD]Gasoline
[/TD]
[/TR]
</tbody>[/TABLE]
What I want to do is look for the text string that appears in the second sheet under "Vendor Name Ref" located in the "Payee" column of the first sheet, and return the "Exp Description" located in the second sheet that matches that Vendor Name Ref and return it in the "Description" column (currently blank) on the first sheet. Kind of like a reverse vlookup with wildcards (I know that it's not possible to do a reverse vlookup with wildcards, so hoping some sort of index/match or array scenario would work?). I'm not as comfortable with arrays yet but feel fairly comfortable understanding most formulas provided they aren't VBA.
90% of the expenses we have will be to vendors we've used before, but adding the descriptions for each vendor's associated expense type manually every month is too time consuming so I'm hoping for a code to write that I can use whenever I download more transactions from the bank (cleaning up the 10% that are new vendors as needed).
I've done hours of searches on this and haven't found anything that sounds like what I'm asking for so I'm turning to you, my fellow Excel-fans in my time of need.
Thank you and happy new year!
Charlotte
I am stuck and am hoping you can help.
I have given up on budgeting software and am attempting my own Excel version of a budget for my household. In a worksheet, I have downloaded all of our bank activity for the year. Many of the transactions are repetitive, but the bank reference includes the vendor name plus non-recurring reference numbers within the text string that vary based on the date of the transaction (so not just a payee name in other words). I was hoping to do a vlookup of sorts that would "read" the vendor name within all the other text and return the type of expense it is. For example, on sheet 1, I have the transactions. Let's say three of them look something like the below:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Trans Date
[/TD]
[TD]Payee
[/TD]
[TD]Amount
[/TD]
[TD]Description
[/TD]
[/TR]
[TR]
[TD]01/01/16
[/TD]
[TD]REF#201601011544 CRAPPY CABLE CO CUST X251A
[/TD]
[TD]150.00
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]01/02/16
[/TD]
[TD]SUNSHINE MARKET REF 1216115QD5667
[/TD]
[TD]80.00
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]01/02/16
[/TD]
[TD]POS PUMP 4 MORRO BAY ACME FUEL STATION
[/TD]
[TD]20.00
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
On the second sheet, I have my "index" references, like this:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Vendor Name Ref
[/TD]
[TD]Exp Description
[/TD]
[/TR]
[TR]
[TD]CRAPPY CABLE
[/TD]
[TD]Internet/Home Phone
[/TD]
[/TR]
[TR]
[TD]ACME
[/TD]
[TD]Gasoline
[/TD]
[/TR]
[TR]
[TD]SUNSHINE
[/TD]
[TD]Groceries
[/TD]
[/TR]
[TR]
[TD]FRED'S GAS
[/TD]
[TD]Gasoline
[/TD]
[/TR]
</tbody>[/TABLE]
What I want to do is look for the text string that appears in the second sheet under "Vendor Name Ref" located in the "Payee" column of the first sheet, and return the "Exp Description" located in the second sheet that matches that Vendor Name Ref and return it in the "Description" column (currently blank) on the first sheet. Kind of like a reverse vlookup with wildcards (I know that it's not possible to do a reverse vlookup with wildcards, so hoping some sort of index/match or array scenario would work?). I'm not as comfortable with arrays yet but feel fairly comfortable understanding most formulas provided they aren't VBA.
90% of the expenses we have will be to vendors we've used before, but adding the descriptions for each vendor's associated expense type manually every month is too time consuming so I'm hoping for a code to write that I can use whenever I download more transactions from the bank (cleaning up the 10% that are new vendors as needed).
I've done hours of searches on this and haven't found anything that sounds like what I'm asking for so I'm turning to you, my fellow Excel-fans in my time of need.
Thank you and happy new year!
Charlotte
