Position of a cell in a range

[daffy333]

Let's say that I have a named range TEST, that refers to an array.


The contents of the array is

{"bob","steve","larry";"larry","frank","steve";"steve","larry","bob"}


In cell A1 is "frank". What would be the formula I would need to determine the "row" position of "frank" (2)? How would I determine the "column" (2)?


Would these formulas work if I was searching for "bob", if I knew that I wanted to find him in the 3rd row? Or the first column?


Maybe I am thinking about this too hard, or its too early in the morning...


Thank you for your help!

Daffy

 

[Luke M]

Not sure about the first question, but the 2nd question is easier to solve. If you know you want to look in the 3rd row, formula would be:

=MATCH("bob",INDEX(TEST,3,),0)

Similarly, if you know the column, you can use the 3rd arguement in the INDEX function like this:

=MATCH("bob",INDEX(TEST,,3),0)


Note that both of these formula give position relative to the array TEST.

 

[daffy333]

Thank you Luke. That is exactly what I thought too. What I wondered was how I could get the equivalent of a ROW() formula to tell me that "frank" was on the second "row" of the array.


Given two arrays

{"bob","steve","larry";"larry","frank","steve";"steve","larry","bob"}.

and

{"bob","steve","larry";"frank","larry","steve";"steve","larry","bob"}


If A1 = "frank"

Is it possible to use a formula to determine that "frank" is on the second "row" of the first array, and then determine what "column" he appears in the second array, when you look on the second row of the second array?


It's probably more confusing to read than it is to write


Thank you

Daffy

 

[Luke M]

I think this works...here's how to get the Row position:

=SUMPRODUCT((test=A1)*(ROW(test)))-ROW(test)+1


Similarly, for column:

=SUMPRODUCT((test=A1)*(COLUMN(test)))-COLUMN(test)+1

 

[daffy333]

Hrmph. It doesn't seem to work for me.


Maybe this is something that cannot be done using native formulas. And I was all set to blather on about the utility of SUMPRODUCT...


Thank you Luke for your help.

 

[NARAYANK991]

Hi ,


The following formula entered as an array formula , will give you the row number offset of your table , in which the looked for value is present :


=MAX((ROW(M3:O5)-ROW(M3))*(M3:O5=A1)) ' Gives a result of 1


Similarly , the following formula entered as an array formula , will give you the column number offset of your table , in which the looked for value is present :


=MAX((COLUMN(M3:O5)-COLUMN(M3))*(M3:O5=A1)) ' Gives a result of 1


Using the =ADDRESS function with the above two formulae as the row and column offsets , will give you the cell address in which the looked for value is present :


=ADDRESS(ROW(M3)+MAX((ROW(M3:O5)-ROW(M3))*(M3:O5=A1)),COLUMN(M3)+MAX((COLUMN(M3:O5)-COLUMN(M3))*(M3:O5=A1))) ' Gives a result of $N$4


The data table is assumed to be from M3 through O5. It is also assumed that the looked for value is present only once in the data table.


Narayan

 

[Luke M]

daffy,

Is the formula giving an error, or bad data?

Also, I got to thinking last night, that the formula doesn't check for multiple instances of "frank". If that might be an issue, need to tweak slightly to get this:

=SUMPRODUCT(MIN((test=A1)*(ROW(test))))-ROW(test)+1


Similarly, for column:

=SUMPRODUCT(MIN((test=A1)*(COLUMN(test))))-COLUMN(test)+1


The MIN will limit it to the first instance that "frank" is found.

 

[daffy333]

Luke and Narayan,


I think the main difficulty lies in the fact that I haven't explained myself well enough.


What I am trying to do is access a range from a closed workbook, by using a named range (in the name manager) "test" that =C:[book.xls]a1:c3


From that named range I can make an array by creating another named range (in the name manager) "PriorTest" using =index("test",,). I probably don't need both named ranges, but for simplicity's sake, this would work to create the array.


That creates my first array. Then I compare it to a similar array with actual cell addresses on a sheet in the open workbook. I would like to compare changes in the rows or the columns, but it appears that you can't get the row of a "pure array", that has no cell address.


I mean I could just create a new sheet and plop the array in there, but where would the fun be in that? It just seems like there should be some way to do this... but I have no idea.


I got the idea when I reviewed this post:

http://chandoo.org/forums/topic/conditional-formatting-of-date-changes-on-different-workbooks


I apologize if I am just leading you both down the road of futility, it was not my intent. Like I say I feel like something that Excel should be able to do natively, and I came to the place where greater minds could grind on the problem. Thank you so much for your time.


Daffy

 

[Luke M]

Any chance you can open the closed workbook? Many XL function lose their capability when the source workbook is closed. =(