Using Index & Match

[howard]

I need a formula to lookup the account number in the source workbook (these are in Col A), pertaining to the decsription in Col B in the destination workbook./

The account description is also in column B in the destination workbook

See Destination workbook sample below

Excel 2010
	B
1	Bank Current Account
2	Bank Rec's Clearing
3	Vehicle Deposits Rcpt Clearing
4	Cash Floats
5	Call Deposits/ Investments
Sheet1

See Source workbook sample below

Excel 2010
	A	B
1	101005	Bank Current Account
2	101005	Bank Rec's Clearing
3	124015	Vehicle Deposits Rcpt Clearing
4	124165	Cash Floats
5	125100	Call Deposits/ Investments
Sheet1

Your assistance in resolving this is most appreciated

 

[Momentman]

What exactly would you like to return? what would the answers look like?

 

[howard]

In the destination workbook , I want the account numbers in A col theat pertains to the description for Eg if "BANK Current account" is in say B2, then A2 must return 101005

I have tried using this formula but get #N/A

=INDEX(B:B,MATCH('[Account Numbers.xlsx]Sheet1'!$A:$A,'[Account Numbers.xlsx]Sheet1'!$B:$B,0))

 

[sylwester.cz]

Hi Howard,

I did it on the example in my workbook where sheet1 has a name Source and sheet2 has a name Destination

In Destination, cell A1 I used below formula and then copied it down to other rows.

=INDEX(Source!$A$1:$B$5,MATCH(Destination!B1,Source!$B:$B,0),)

If you have Source & Destination in different workbooks then you need to update the code with the direct paths.

Hope it works.

Sylwester

 

[Momentman]

Like this

Excel 2013
	A	B
1	101005	Bank Current Account
2	101005	Bank Rec's Clearing
3	124015	Vehicle Deposits Rcpt Clearing
4	124165	Cash Floats
5	125100	Call Deposits/ Investments
Sheet10

Cell Formulas
Range	Formula
A1	=INDEX('[GTBank-areaGraph.xlsx]Sheet2'!A$1:A$5,MATCH(B1,'[GTBank-areaGraph.xlsx]Sheet2'!B$1:B$5,0),1)

where you replace the "Gtbank-area-graph" with the name of your own workbook

 

[howard]

Thanks for the help, much appreciated