Look for Multiple Matches for a given range

[tayyabq8]

I'm in need of your help to figure out the following. I have following lookup data:

<code>Account Code1    Account Code2    Description1    Description 2 
2500                              B/Sheet        Receivable 
2508                              B/Sheet        Payable 
2504                              PL        Sales</code>

I'm looking for Account code 1 in following range and pickup all the instances of Code 1 in this range and put in Account Code 2.

<code>
Account Code1    Account Code2 
2500             20100 
2508             45000 
2500             20300 
2504             24000 
2500             70000 
2508             40300</code>

This is the output which I'm looking for:

<code>Output              
Account Code1    Account Code2    Description1    Description 2              
2500             20100            B/Sheet         Receivable 
2500             20300            B/Sheet         Receivable 
2500             70000            B/Sheet         Receivable 
2508             45000            B/Sheet         Payable 
2508             40300            B/Sheet         Payable 
2504             24000            PL              Sales</code>

I am looking for a VBA code which looks for all possible matches for a given number and inserts those many rows.

I know it is achievable in Excel but I am very naive when it comes to Excel related stuff, can someone please help me with this?

Regards,

 

[Simon Lloyd]

You really only need to use VlookUp, take your data, lets say its sheet1 that your complete data is on and you want to populate sheet2 Code2, enter this formula in sheet2 code2's first available cell and then copy down =VLOOKUP($A2,Sheet1!$A:$B,2,FALSE)

 

[tayyabq8]

You really only need to use VlookUp, take your data, lets say its sheet1 that your complete data is on and you want to populate sheet2 Code2, enter this formula in sheet2 code2's first available cell and then copy down =VLOOKUP($A2,Sheet1!$A:$B,2,FALSE)

Hello Simon

Thanks for your reply. Problem is that, my data and lookup range (where im looking for match) has one-to-many relation, means for 2500, I have got more than one match and I don't know how many? It is random. Vlookup always returns the first match and I have no way to look for rest of the matches. Any ideas please?

 

[Simon Lloyd]

Looking at your data how will you possibly be able to to tell which number belongs to which code, take 2500, by searching through the available 2500's on the data sheet how will you be able to distinguish which code2 gets put next to which 2500 code?

 

[Useful]

Hi,
try ths with CSE (Ctl+Shift+Enter)
in this example
$B$1:$B$10 is your result column (where u want to get account code2 for account code1)
A$1:A$10 is account code1 range
$E1 - is your critera

=INDEX($B$1:$B$10,SMALL(IF((A$1:A$10=$E1)*(ROW(A$1:A$10))<>0,(A$1:A$10=$E1)*(ROW(A$1:A$10))),COUNTIF($E$1:E1,E1)),1)