return a column no

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Craig McLaughlin - 13 Jan 2008 21:35 GMT

I have a cell containing text. I need a formula that takes the text and
finds an exact match in a row and return the column no.

Eg

Find Text: Name3

Name1 Name2 Name3 Name4 Name5

I want to return the column number which in this case is row 3.

I know there is a simple formula but I cant find it

Thanks

Reply to this Message

Dave Peterson - 13 Jan 2008 22:06 GMT

=match(a2,1:1,0)
will return the number of the column of the first match (if there is one).

Assumes that the Name3 is in A2 and the names are listed in A1:IV1 (row 1)

> I have a cell containing text. I need a formula that takes the text and
> finds an exact match in a row and return the column no.
[quoted text clipped - 10 lines]
>
> Thanks

Signature

Dave Peterson

Reply to this Message

Craig McLaughlin - 15 Jan 2008 23:03 GMT

thanks to everyone for their help

Craig

> =match(a2,1:1,0)
> will return the number of the column of the first match (if there is one).
[quoted text clipped - 15 lines]
>>
>> Thanks

Reply to this Message

T. Valko - 13 Jan 2008 22:09 GMT

A1 = Name3

A5:E5 = Name1,Name2,Name3,Name4,Name5

This will return the *relative* position of the lookup_value in the
lookup_array:

=MATCH(A1,A5:E5,0)

=3

Signature

Biff
Microsoft Excel MVP

>I have a cell containing text. I need a formula that takes the text and
>finds an exact match in a row and return the column no.
[quoted text clipped - 10 lines]
>
> Thanks

Reply to this Message

CLR - 13 Jan 2008 22:10 GMT

Maybe this...........

=MATCH("NAME3",A1:E1,0)

Vaya con Dios,
Chuck, CABGx3

> I have a cell containing text. I need a formula that takes the text and
> finds an exact match in a row and return the column no.
[quoted text clipped - 10 lines]
>
> Thanks

Reply to this Message