match an array with a criteria then outputs row number of ma

[edy]

hi,
i'm trying to search for matches on an array (a1:a4)with b1 then B2 and so on as the criteria. Then display the row number as an output (column c)as shown

a b c
1 6 7 2
2 7 8 3
3 8 6 1
4 9 12 7
5 10
6 11
7 12
i have try this statement
if (countif ($a$1:$a$7,b1),row()
it just won't dislay the right row number of the match. all the entries for A and b are unique.

thanks
This message was edited by edy on 2002-11-01 00:37

 

[eliW]

On 2002-11-01 00:36, edy wrote:
hi,
i'm trying to search for matches on an array (a1:a4)with b1 then B2 and so on as the criteria. Then display the row number as an output (column c)as shown

a b c
1 6 7 2
2 7 8 3
3 8 6 1
4 9 12 7
5 10
6 11
7 12
i have try this statement
if (countif ($a$1:$a$7,b1),row()
it just won't dislay the right row number of the match. all the entries for A and b are unique.

thanks
This message was edited by edy on 2002-11-01 00:37

Hi edy and welcome to the board.

You can use simple MATCH here which will do exactly what you need:

=MATCH(B1,$A$1:$A$7)

Copy to other rows.

Eli

 

[Yogi Anand]

Hi edy:

Welcome to the Board!

You can use the following formula in cell C1 and then copy down:

=MATCH(B1,$A$1:$A$7,0)

or you can use the following array formula:

{=MATCH(B1:B4,$A$1:$A$7,0)}

Regards!
Yogi Anand