VLOOKUP or something else to compare two lists AND associated balances for each list

[maize]

I'm trying to compare two separate sheets of data: Sheet A, a list of financial accounts and associated balances from last year, and Sheet B, a list of accounts and associated balances from this year. Both sheets contain the same column headers (Unique account number, account name, and balance), but the two lists of accounts are slightly different, and the balances are different. I would like to know several things:

1. Which accounts have been closed: That is, they existed last year and are listed on Sheet A but do NOT exist this year and are not listed on Sheet B?
2. Which accounts are new: That is, they exist this year and are listed on Sheet B but did NOT exist last year and are not on Sheet A?
3. For the accounts that are listed in both Sheet A and Sheet B, what is the difference between the balance last year and the balance this year?

I have been trying to do this with either VLOOKUP or INDEXMATCH functions, but that doesn't give me all the information I'm looking for.

There must be a relatively elegant way to do this! Can anyone point me in the right direction? Many thanks. :-)

[K m]

Do you have any sample data?

General guidance:

- You could do a lookup specifically VLOOKUP or MATCH for items on Sheet A.
If there was no match on Sheet B, the accounts are closed

- You could do a lookup specifically VLOOKUP or MATCH for items on Sheet B.
If there was no match on Sheet A, the accounts are new

- If MATCH or VLOOKUP produce a match compare the balances

[K m]

Do you have any sample data?

General guidance:

- You could do a lookup specifically VLOOKUP or MATCH for items on Sheet A.
If there was no match on Sheet B, the accounts are closed

- You could do a lookup specifically VLOOKUP or MATCH for items on Sheet B.
If there was no match on Sheet A, the accounts are new

- If MATCH or VLOOKUP produce a match compare the balances

[maize]

Thank you for your help!

I can figure out how to do all these things separately. The problem is that I would like to be able to do them all at once. Sample spreadsheet attached. Thank you!Sample spreadsheet Maize.xlsx

[K m]

if you add a test for errors, you will remove the error. For example:

=IFERROR(INDEX('2011'!$C$3:$C9,(MATCH(Compare!A6,'2011'!$A$3:$A$5,0))),"")

[maize]

Could you clarify what you mean, K m? I don't want to remove the error, because it isn't really an error--it is correctly showing me that there was no account #3004 in 2011--that account was opened in 2012.

I want to see information like that and also see which accounts were open in 2011 but closed in 2012. Thanks for the help.

[K m]

You are correct there really isn't a error, but most people would rather see a blank than a cell with a #NA.

The suggestion above would eliminate the #NA

As far as the closed accounts, you can compare lists 1 and 2 and an account that is on list 1 but not 2, it is closed

[maize]

Yes, I see. That does look nicer.