display the cell ref a matching number is in

  • Thread starter Thread starter Cadders
  • Start date Start date
C

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?
 
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
 
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 :-(
 
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
 
ahh I see....thanks again Mike for your help

Mike H said:
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
Click to expand...
 
Back
Top