display the cell ref a matching number is in

[Cadders]

Please can someone help me with the following query?

If i have a given number and there is a matching number within a row, how
can I return the actual cell ref that the matching number is in please?

 

[Mike H]

Try this

=ADDRESS(ROW(A1),MATCH($P$1,A1:N1,0))

Drag down and it searches each row starting in row 1 for the address of the
value in P1


Mike

 

[Cadders]

Thanks again Mike....can you tell me what the 'Row(a1)' dictates please? the
cell I am matching is N4 and the range to match it against is I4:M4. When I
use your formula and change the 'Match' part, it returns the cell ref of B4
but the matching cell is actually J4 - sorry for being numb :-(

 

[Mike H]

Hi,

Row(a1) is simply a method of getting a number 1 so that the formula looks
in row 1. The advantage od doing that instead of just entering a 1 is that if
you grag it down then Row(a1) increments to Row(a2) and the formula looks at
the next row.

For your data layout try this
=ADDRESS(ROW(A4),MATCH(N4,I4:M4,0))

once again Row(a4) can simply be a 4

Mike

 

[Cadders]

ahh I see....thanks again Mike for your help

Hi,

Row(a1) is simply a method of getting a number 1 so that the formula looks
in row 1. The advantage od doing that instead of just entering a 1 is that if
you grag it down then Row(a1) increments to Row(a2) and the formula looks at
the next row.

For your data layout try this
=ADDRESS(ROW(A4),MATCH(N4,I4:M4,0))

once again Row(a4) can simply be a 4

Mike