If text in column A matches text in column B then use data from column C

[kemble]

I have done several searches and tried several formulas from lookup to IF to Match etc and cant get the correct result.

I'm attempting to edit my bank downloaded monthly statement Quickbooks IIF File in excel as text tab delimited and edit the banks generic entry names to my actual account names in Quick Books so when I import the .IIF file into Quick Books then each entry is filed in the appropriate account and doesn't create a bunch of new accounts.

I'm working with 3 Sheets.

Sheet1 will be the downloaded file from my bank that has generic names like "STAMPS.COM *USPOSTAG 877-782-67" in Column E
Sheet2 will be the formulas that will rename the generic name from "STAMPS.COM *USPOSTAG 877-782-67" to "Shipping Expenses:USPS Postage Purchase"
Sheet3 will have 2 columns. Column A will be the bank generic name "STAMPS.COM *USPOSTAG 877-782-67". Column B will be the Quick Books account name "Shipping Expenses:USPS Postage Purchase".

My fail attempts at the formula looks like this:

=INDEX(Sheet1!E:E,MATCH(Sheet3!A:A,Sheet3!B:B,0))
=IFERROR(VLOOKUP(Sheet1!E:E,Sheet3!A:A,Sheet3!B:B),"(NO MATCH)")
=IF(Sheet1!E:E=Sheet3!A:A,Sheet3!B:B)


Once all entries in Sheet1 have been renamed on Sheet2 then I will copy/paste values in Sheet2 into a new sheet4, then save as tab delimited and then rename the .txt file to .IIF and import to Quickbooks.

Thank you in advance !!

[mikeTRON]

Can you post a sample workbook?

I don't think you understand how to use VLOOKUP, INDEX or IF formulas judging by your failed attempts.

[kemble]

I don't want to post a sample workbook as the document contains financial data for my business.

Basically I'm looking for a formula that will search for data in column E on sheet1 and match it with column A on sheet3, when it finds the match then it will display in column E in sheet2 the data that is entered in column B on sheet3.

Sheet1 is the actual downloaded bank statement in tab delimited form
Sheet2 is the formula sheet.
Sheet3 is my "Key" It will have a list of about 200 different bank default transaction names in column A and next to it in the same row in column B it will have the value that I want displayed (the name of the actual account/category in Quick Books).

If you are unable to help based off this information then please let me know and I'll just give up and manually copy/paste everything. I was just looking for a method to save me time every month.

[JohnTopley]

A small file (less than 20 rows) with non-confidential data is all that is required.

[kemble]

File is attached

[kemble]

Also to add...

If the formula does not find a match, then place value "Other Expenses"

[JohnTopley]

In E4

=IFERROR(VLOOKUP(Sheet1!E4,Sheet3!$A$2:$B$11,2,0),"Other Expenses")

in E5

=IFERROR(VLOOKUP(Sheet1!E5heet3!$A$2:$B$11,2,0),"Other Expenses")

Select E4 & E5 and copy/paste to your other "pairs"

OR

in E1 and copy down

=IF(Sheet1!E1="","",IF(Sheet1!E1="ACCNT","ACCNT",IFERROR(VLOOKUP(Sheet1!E1,Sheet3!$A$2:$B$100,2,0),"Other Expenses")))

[kemble]

You sir are AMAZING !!!

THANK YOU THANK YOU THANK YOU !!!

[JohnTopley]

Thank for the feedback and rep.

Could you please mark thread as solved ("Thread Tools" at top of first post).

[kemble]

After going over everything I'm noticing that a few of the bank entries all start with the same first 4 letter but then have random letters after it (there will be 26 of these every year, it appear they will never repeat the same entry). These are all supposed to go in the same Quick Books Account. Is there a way to do this or no?

For example:
Bank Enties
AMZNIOPMSKOEL82634
AMZNNEMSKWI8104022
AMZNRNJLOO26739420
AMZNIINSWN88293421

All of these should go into Quick Books Account: "Amazon Merchant Account"

For example:
PAYPAL INST XFER PAPE
PAYPAL *PAYMENTPRO 402-935-7733
PAYPAL INST XFER STEA
PAYPAL INST XFER EXPR
PAYPAL INST XFER RUMB
PAYPAL INST XFER DNH*

All of these should go in "Paypal Merchant Account"

[JohnTopley]

You have an "Amazon Marketplace Account" which also starts with AMZN: is this ("Amazon Merchant Account" ) a different a/c? If so, how do distinguish between the two?

[kemble]

im sorry, I meant "Amazon Marketplace Account" not Amazon Merchant Account

[JohnTopley]

Assuming it is one Amazon a/c then ...


=IF(Sheet1!E1="","",IF(Sheet1!E1="ACCNT","ACCNT",IF(LEFT(Sheet1!E1,4)="AMZN","Amazon Marketplace Account",IFERROR(VLOOKUP(Sheet1!E1,Sheet3!$A$2:$B$100,2,0),"Other Expenses"))))

Hopefully there aren't too many more "anomalies".

[kemble]

So far, Amazon's bi-weekly deposits (26 a year) do this, where every deposit starts with the same 4 letters "AMZN" and then random 10 letters/numbers after it.

and PayPal Instant Transfers do this as well. A PayPal instant transfer happens when I purchase something with my PayPal account and there is not enough money in the PayPal account so it pulls the rest of the funds from my attached bank account which appears this follows the same system, where each transfer starts out with "PAYPAL INST XFER " and then 4 random letters. This could happen 100+ times a year for all I know.

I will attempt to follow your last formula and see if I can add PayPal to it :P

[kemble]

=IF(Sheet1!E1="","",IF(Sheet1!E1="ACCNT","ACCNT",IF(LEFT(Sheet1!E1,4)="AMZN","Amazon Merchant Account",IF(LEFT(Sheet1!E1,10)="PAYPAL ","PAYPAL Merchant Account",IFERROR(VLOOKUP(Sheet1!E1,Sheet3!$A$2:$B$300,2,0),"Other Expenses")))))

Works !!

Thanks again !!

[JohnTopley]

Just do a similar test:

IF(LEFT(Sheet1!E1,6)="PAYPAL","Paypal account......",