Although I consider myself fairly good with Excel (I use 2003) I have
never used VBA.

I want to achieve something and feel a macro is the only way I can
achieve it.

Imagine I have the following in Excel:

1 A B C D
2 test 0
3 abc 0
4 abc123 1
5 something 1
6 text 1
7 hello 0

I want a cell, say, D1, to display the corresponding text in column A
for where it finds the first instance of a '1' in the B column. So it
would find a 1 in B4 and therefore display the text 'abc123' in cell
D1. It would then stop looking and ignore the 1's in B5 and B6.

Any help in this would be greatly appreciated!

Many thanks,
Mat G
Birmingham, UK

Hi,

No need for a macro, try this

=INDEX($A$1:$A$6,MATCH(1,$B$1:$B$6,FALSE),1)

The 1 (in the middle of the formula) could and would be better replaced with
a cell reference.

Mike

"(E-Mail Removed)" wrote:

> Although I consider myself fairly good with Excel (I use 2003) I have
> never used VBA.
>
> I want to achieve something and feel a macro is the only way I can
> achieve it.
>
> Imagine I have the following in Excel:
>
> 1 A B C D
> 2 test 0
> 3 abc 0
> 4 abc123 1
> 5 something 1
> 6 text 1
> 7 hello 0
>
> I want a cell, say, D1, to display the corresponding text in column A
> for where it finds the first instance of a '1' in the B column. So it
> would find a 1 in B4 and therefore display the text 'abc123' in cell
> D1. It would then stop looking and ignore the 1's in B5 and B6.
>
> Any help in this would be greatly appreciated!
>
> Many thanks,
> Mat G
> Birmingham, UK
>

That is brilliant - just what I needed! I have never used that feature
before - every day is a school day!!

Thanks again,

Mat


On Apr 25, 12:40*pm, Mike H <Mi...@discussions.microsoft.com> wrote:
> Hi,
>
> No need for a macro, try this
>
> =INDEX($A$1:$A$6,MATCH(1,$B$1:$B$6,FALSE),1)
>
> The 1 (in the middle of the formula) could and would be better replaced with
> a cell reference.
>
> Mike
>
>
>
> "m...@whs-halo.co.uk" wrote:
> > Although I consider myself fairly good with Excel (I use 2003) I have
> > never used VBA.
>
> > I want to achieve something and feel a macro is the only way I can
> > achieve it.
>
> > Imagine I have the following in Excel:
>
> > 1 *A * * * B * * * C * * * D
> > 2 *test * *0
> > 3 *abc * * 0
> > 4 *abc123 *1
> > 5 *something * * * 1
> > 6 *text * *1
> > 7 *hello * 0
>
> > I want a cell, say, D1, to display the corresponding text in column A
> > for where it finds the first instance of a '1' in the B column. So it
> > would find a 1 in B4 and therefore display the text 'abc123' in cell
> > D1. It would then stop looking and ignore the 1's in B5 and B6.
>
> > Any help in this would be greatly appreciated!
>
> > Many thanks,
> > Mat G
> > Birmingham, UK- Hide quoted text -
>
> - Show quoted text -