Match two values and return another

[Daviduk]

Hi everyone, I have two sets of data, both containing Account Names, and Balances, however one set of data also contains account numbers.

My quest is this, I wish to match Account Name and Balance to return account number.

Sheet 1 contains 200 accounts with Name and Balance only
Sheet 2 contains 500 accounts with Name and balance and account number.

Please note that in some instances on the list with 500 there are duplicate names with two account numbers so for example,

St Johns School £5676 - ( A133543 (Being the acct #))
St Johns School £44567 - A454563

So as I said, I need to find the exact St Johns School by Name and balance and return the account number on Sheet 1.

Thanks in advance

David.

 

[John Davis]

Hi everyone, I have two sets of data, both containing Account Names, and Balances, however one set of data also contains account numbers.

My quest is this, I wish to match Account Name and Balance to return account number.

Sheet 1 contains 200 accounts with Name and Balance only
Sheet 2 contains 500 accounts with Name and balance and account number.

Please note that in some instances on the list with 500 there are duplicate names with two account numbers so for example,

St Johns School £5676 - ( A133543 (Being the acct #))
St Johns School £44567 - A454563

So as I said, I need to find the exact St Johns School by Name and balance and return the account number on Sheet 1.

Thanks in advance

David.

David:

There are more efficient ways to accomplish this but In some cases, I'm still stuck in my old habits.

For your situation I would insert a helper column in both sheets. I would Concatenate the Name and Balance on both. Then you could use a VLOOKUP on Sheet 1 to pull the proper Account Number from Sheet 2.

 

[MikeWx]

David,

If you are looking for a solution without a helper column, the following formula assumes that the Sheet2 Name, Balance, and Account Number are in A2:C501, and that the Sheet1 Name and Balance are in A2:B201. Enter the following array formula in Sheet1 Cell C2:

=INDEX(Sheet2!$C$2:$C$501,MATCH(A2&B2,Sheet2!$A$2:$A$501&Sheet2!$B$2:$B$501,0))

Press Ctrl-Shift-Enter instead of just Enter. Copy the formula down to Cell C201 of Sheet1.