Cell Address

[Gaurav]

Hi,

What formula do I need to use to accomplish the following.

Lookup some value in another sheet and return the Cell Address(Column and
Row) that has this value.

Thanks in advance.

 

[Mike H]

Hi,

It depends on how the data we are looking up are laid out but here's a
simple one

="A" & MATCH(A1,Sheet2!A:A,FALSE)

Mike

 

[Gaurav]

Thanks Mike. I have one more question.

How can I use the value returned by this formula in a COUNTIF function. I
mean the formula you just wrote in your reply gives me the row number and
now I want to know how many times a particular value appears in that this
row.

Thanks a ton.

 

[Mike H]

Hi,

One way

=COUNTIF(INDIRECT("Sheet2!" & MATCH(A1,Sheet2!A:A,FALSE)&":"&
MATCH(A1,Sheet2!A:A,FALSE)),"Myvalue")

All in one line. If you are trying to count number change "Myvalue" to the
number you looking for with no quotes.

Mike