formula to display cell address

[Jamie]

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

 

[zvkmpw]

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.

 

[Jamie]

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.

 

[ghoods]

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