I have a worksheet in which I want to compare the data in one cell (A1) with
that in another cell. The cell which is to be compared against is immediately
beneath A1 but being one of several which will be filtered, may not be Cell
A2. It could be any one of cells A2 - A10 for example. Can any one help me in
creating a function?

If I understand correctly, you have a value in A1, and you want to determine
if this same value exists anywhere from A2 to A10. If so then you need to
look at the MATCH function. For example in B2 type:-

=IF(MATCH(A1,A2:A10,0)0,"Found","Not Found")

Hope this helps.
--
Alan Moseley IT Consultancy
http://www.amitc.co.uk

If I have solved your problem, please click Yes below. Thanks.


"Bryan Gnidrah" wrote:

I have a worksheet in which I want to compare the data in one cell (A1) with
that in another cell. The cell which is to be compared against is immediately
beneath A1 but being one of several which will be filtered, may not be Cell
A2. It could be any one of cells A2 - A10 for example. Can any one help me in
creating a function?

Hi Alan,

Thanks for response. My problem is in fact the inverse of your proposed
solution. However, I think I have found a solution, rather than comparing A1
with a variable row from the range A2-A10, I can compare the specific row,
say A7 with A1, placing the function in a cell in the variable row.

"Bryan Gnidrah" wrote:

I have a worksheet in which I want to compare the data in one cell (A1) with
that in another cell. The cell which is to be compared against is immediately
beneath A1 but being one of several which will be filtered, may not be Cell
A2. It could be any one of cells A2 - A10 for example. Can any one help me in
creating a function?

This array formula** will extract the *first* item in a filtered list.

=INDEX(A2:A10,MATCH(1,SUBTOTAL(3,OFFSET(A2:A10,,,R OW(A2:A10)-ROW(A2)+1)),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Bryan Gnidrah" wrote in message
...
I have a worksheet in which I want to compare the data in one cell (A1)
with
that in another cell. The cell which is to be compared against is
immediately
beneath A1 but being one of several which will be filtered, may not be
Cell
A2. It could be any one of cells A2 - A10 for example. Can any one help me
in
creating a function?