If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Excel "Exact" Function
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? |
#2
|
|||
|
|||
Excel "Exact" 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? |
#3
|
|||
|
|||
Excel "Exact" 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? |
#4
|
|||
|
|||
Excel "Exact" 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? |
Thread Tools | |
Display Modes | |
|
|