If code help

[Jazzer]

Hi All<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I am looking for some code / formula to help with the following issue<o></o>
<o></o>
from B8 to B50 I want to look in col F (between 7 & 500) for any cell which contains :35B:ISIN when this is found I want to return the result from col G on the corresponding row back into Col B starting in 8 and then so on with the next result all the way down to 50<o></o>
<o></o>
&<o></o>
<o></o>
from C8 to C50 I want to look in col F (between 7 & 500) for any cell which contains :70E::HOLD// (this will always be followed immediately by a number I want to return the number into col C starting in 8 and then so on with the next result all the way down to 50<o></o>
<o></o>
if anybody has any suggestion it would be much appreciated <o></o>
<o> </o>
many Thanks<o></o>
<o> </o>

 

[Saagar]

Here is the solution for the first part of your request. This can be done with the use of array formulas. To learn about array formulas check these links:
http://office.microsoft.com/en-us/excel-help/introducing-array-formulas-in-excel-HA001087290.aspx
http://www.cpearson.com/excel/arrayformulas.aspx

Copy and paste this formula in cell B8:
= INDIRECT( "G" & SMALL(IF($F$7:$F$500=":35B:ISIN",ROW($F$7:$F$50),999999),ROW(B8)-7))

After you copy it, instead of hitting ENTER, you have to hit Ctrl-Shift-Enter together.

After the formula is entered, if you highlight cell B8, you will notice that excel adds braces to the formula at either end as shown below. If you don't see the braces Excel did not convert the formula in to an array formula.
{= INDIRECT( "G" & SMALL(IF($F$7:$F$500=":35B:ISIN",ROW($F$7:$F$50),999999),ROW(B8)-7))}

You can drag the formula from B8 to B50.

Test it and let me know if it is working the way you want.