I need a formula to display a cell address

I want C1 to compare B1 to A1:A14 and bring back the cell reference if there
is a match.
In the below example C1 should read A2.
Thanks in advance for any help.

A B C
1 5 10
2 10 20
3 15 30
4 20 40
5 25 50
6 30 60
7 35 70
8 40 80
9 45 90
10 50 100
11 55 110
12 60 120
13 65 130
14 70 140

I want C1 to compare B1 to A1:A14 and bring back the cell reference if there
is a match.
In the below example C1 should read A2.
Thanks in advance for any help.

* * * * A * * * B * * * C
1 * * * 5 * * * 10 * * *
2 * * * 10 * * *20 * * *
3 * * * 15 * * *30 * * *
4 * * * 20 * * *40 * * *
5 * * * 25 * * *50 * * *
6 * * * 30 * * *60 * * *
7 * * * 35 * * *70 * * *
8 * * * 40 * * *80 * * *
9 * * * 45 * * *90 * * *
10 * * *50 * * *100 * *
11 * * *55 * * *110 * *
12 * * *60 * * *120 * *
13 * * *65 * * *130 * *
14 * * *70 * * *140 * *

One way is to put this in C1
=IF(COUNTIF(A:A,B1)0,ADDRESS(MATCH(B1,A:A,0),1,4, 1),"no match")
and copy downward.

Modify to suit.

"zvkmpw" wrote:

I want C1 to compare B1 to A1:A14 and bring back the cell reference if there
is a match.
In the below example C1 should read A2.
Thanks in advance for any help.

A B C
1 5 10
2 10 20
3 15 30
4 20 40
5 25 50
6 30 60
7 35 70
8 40 80
9 45 90
10 50 100
11 55 110
12 60 120
13 65 130
14 70 140

One way is to put this in C1
=IF(COUNTIF(A:A,B1)0,ADDRESS(MATCH(B1,A:A,0),1,4, 1),"no match")
and copy downward.

Modify to suit.

Outstanding! I bow to your Excel genius! Thank you again.

zvkmpw wrote:
I want C1 to compare B1 to A1:A14 and bring back the cell reference if there
is a match.
[quoted text clipped - 16 lines]
13 Â* Â* Â*65 Â* Â* Â*130 Â* Â*
14 Â* Â* Â*70 Â* Â* Â*140 Â* Â*

One way is to put this in C1
=IF(COUNTIF(A:A,B1)0,ADDRESS(MATCH(B1,A:A,0),1,4, 1),"no match")
and copy downward.

Modify to suit.


Great post, I am learner about excel. i need some more this type of posts
which help me to get excel knowledge.

hope you will help me in future about excel help.

Thanks !!!

_____________________
http://www.microsoftoffice-2010.com