# Using Index & Match

#### howard

##### Well-known Member
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
1Bank Current Account
2Bank Rec's Clearing
3Vehicle Deposits Rcpt Clearing
4Cash Floats
5Call Deposits/ Investments
Sheet1

See Source workbook sample below

Excel 2010
AB
1101005Bank Current Account
2101005Bank Rec's Clearing
3124015Vehicle Deposits Rcpt Clearing
4124165Cash Floats
5125100Call Deposits/ Investments
Sheet1

Your assistance in resolving this is most appreciated

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

#### Momentman

##### Well-known Member
What exactly would you like to return? what would the answers look like?

#### howard

##### Well-known Member
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

##### New Member
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.
Code:
``=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

##### Well-known Member
Like this

Excel 2013
AB
1101005Bank Current Account
2101005Bank Rec's Clearing
3124015Vehicle Deposits Rcpt Clearing
4124165Cash Floats
5125100Call Deposits/ Investments
Sheet10
Cell Formulas
RangeFormula
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

##### Well-known Member
Thanks for the help, much appreciated

Replies
10
Views
781
Replies
4
Views
3K
Replies
1
Views
2K
Replies
0
Views
544
Replies
11
Views
2K

1,196,013
Messages
6,012,857
Members
441,736
Latest member
Tkpmm

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back