Cell Address Matching
 

Hi everyone,

Please treat this as an urgent message.

I have a worksheet as

5 2 1 3 1 3 1
8 1 2 0 4 6 1


Value Cell Address `
2 C2

I would like to put a value in the *value box *and it return in which
cell address is the value.

for example if enter 2 I want to display in which cell address contain
2.

my email is

your help is very much appreciated.

Regards

Khalid


--
khalid
------------------------------------------------------------------------
khalid's Profile: http://www.excelforum.com/member.php...o&userid=29916
View this thread: http://www.excelforum.com/showthread...hreadid=496123

Cell Address Matching
 

Assumptions:

You wish to return the cell address where the value last appeared (last
row, and last cell in that row where the value appears more than once)

A1:G2 contains your data

J1 contains the value of interest, such as 2

Formula:

=CELL("address",INDEX(A1:G2,MAX(IF(A1:G2=J1,ROW(A1 :G2)-ROW(A1)+1)),MATCH(
2,1/(INDEX(A1:G2,MAX(IF(A1:G2=J1,ROW(A1:G2)-ROW(A1)+1)),0)=J1))))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

In article ,
khalid wrote:

Hi everyone,

Please treat this as an urgent message.

I have a worksheet as

5 2 1 3 1 3 1
8 1 2 0 4 6 1


Value Cell Address `
2 C2

I would like to put a value in the *value box *and it return in which
cell address is the value.

for example if enter 2 I want to display in which cell address contain
2.

my email is

your help is very much appreciated.

Regards

Khalid