Cell Address Matching

K

khalid

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 whic
cell address is the value.

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

my email is [email protected]

your help is very much appreciated.

Regards

Khali
 
D

Domenic

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!
 
Top