Looking for correct Index / Match formula

[shaikhrulez]

Hello,

Please refer to attached file.

I got data in Column A & B, column A contains all branch locations & column B contains its account code.

Column D contains OUR BRANCHES I need account code for them only, though it can be achieved by using filter but taking a lot of time; as you can see there are hefty record for a particular branch.

I tried by copying ALL BRANCHES data to column F and then applying Index / Match formula in column G but I guess I'm missing something here.. Can anyone help me out?

Thanks

 

[Nebu]

Hi:

Your index formula is wrong.

use the following

=INDEX($B$2:$B$32008,MATCH($D86,$A$2:$A$32008,0),1)

if your getting N/A if your branch number is not available in the main list wrap it with an iferrror.

Thanks

 

[shaikhrulez]

Hi:

Your index formula is wrong.

use the following

=INDEX($B$2:$B$32008,MATCH($D86,$A$2:$A$32008,0),1)

if your getting N/A if your branch number is not available in the main list wrap it with an iferrror.

Thanks

Thanks @Nebu for answering.

but the formula you've provided above isn't working as well. It is giving the same result. Can you please re-check the attachment?

 

[vletm]

One possible ...
col-K Branche
col-L ACC_CODE
and
col_N all ACC_CODE's for same row Branche
Press [ SOLVE ] to resolve.

 

[Nebu]

Hi:

My formula, in the match part you have given it wrong in the cell I2 in match construct it should not be D86, but D2. But I guess your requirement is different , the above post has given you a macro solution , I am not sure how helpful it will be for you since the output is comma separated. If the above macro output is not serving your purpose let me know this can be achieved through a simple macro, as you mentioned in your post before using filters.

Thanks

 

[shaikhrulez]

One possible ...
col-K Branche
col-L ACC_CODE
and
col_N all ACC_CODE's for same row Branche
Press [ SOLVE ] to resolve.

Thanks @vletm, I was wondering if I can get the result in separate rows against each branch as shown in data Column A & B. Isn't this possible?

 

[shaikhrulez]

Hi:

My formula, in the match part you have given it wrong in the cell I2 in match construct it should not be D86, but D2. But I guess your requirement is different , the above post has given you a macro solution , I am not sure how helpful it will be for you since the output is comma separated. If the above macro output is not serving your purpose let me know this can be achieved through a simple macro, as you mentioned in your post before using filters.

Thanks

I tried doing that as always, putting D2 instead of D86 giving the same result as mine.
Yeah, the solution provided by vletm has got some short coming, Output is in single cell & also separated by comma. I'll need few more steps (Text to Column & Transpose) in order to bring it into shape.

It would be helpful if you can provide a simple formula or macro based solution to this.

Thanks

 

[vletm]

I hided some columns and added Texts to Columns.
I modified 'separated by comma' column too. If You want it back, just say.
You can 'name' those Columns as You want; check comments in columns.

 

[shaikhrulez]

I hided some columns and added Texts to Columns.
I modified 'separated by comma' column too. If You want it back, just say.
You can 'name' those Columns as You want; check comments in columns.

Thanks @vletm you saved my day..