Try this array formula
=INDEX(A1:I1,MIN(IF(B2:I9="Q1",COLUMN(B2:I9))))&"-"&INDEX(A1:A9,MIN(IF(B2:I9="Q1",ROW(B2:I9))))
This is a discussion on Reverse Index/Match? within the Excel Questions forums, part of the Question Forums category; Hey all: I've got a chess board in B2:I9, and would like a function that returns the location of a ...
Hey all:
I've got a chess board in B2:I9, and would like a function that returns the location of a particular piece.
Say I have Q1 in cell B9, I'd like to return A-1
B1:I1 = A ---> H
A2:A9 = 8 ---> 1
Any help?
Try this array formula
=INDEX(A1:I1,MIN(IF(B2:I9="Q1",COLUMN(B2:I9))))&"-"&INDEX(A1:A9,MIN(IF(B2:I9="Q1",ROW(B2:I9))))
Another array formula, on the same idea as xld's, but robust, in L3:
=IF(COUNTIF($B$2:$I$9,K3)=0,"",INDEX($B$1:$I$1&"-"&$A$2:$A$9,MAX(IF($B$2:$I$9=K3,ROW($B$2:$I$9)-ROW($B$2)+1)),MAX(IF($B$2:$I$9=K3,COLUMN($B$2:$I$9)-COLUMN($B$2)+1))))
... confirmed with CTRL+SHIFT+ENTER.
Copy down
A B C D E F G H I J K L M 1 A B C D E F G H 2 1 Q1 Piece Location 3 2 T2 Q1 A-1 4 3 T2 C-2 5 4 R1 6 5 K2 F-7 7 6 8 7 K2 9 8 10 [Book1]Sheet2
Kind regards
PGC
To understand recursion, you must understand recursion.
xld's formula can be modified a little...
=CHAR(MAX(IF($B$2:$I$9="Q1",COLUMN($B$2:$I$9)+63,32)))&"-"&CHAR(MAX(IF($B$2:$I$9="Q1",58-ROW($B$2:$I$9),32)))
The purpose of this variation is to eliminate some unnecessaries: to avoid the lookup by reconstructing the characters directly and to thus sidestep the separate "robustness" check. It doesn't add any. It isn't necessarily better as a result; it's just an alternative.
Last edited by Jobowo; Dec 5th, 2009 at 03:56 PM.
...assuming that you weren't referring to the fetch of variable values from other cells..
=CHAR(MAX(IF($B$2:$I$9=$K3,COLUMN($B$2:$I$9)+63,32)))&"-"&CHAR(MAX(IF($B$2:$I$9=$K3,58-ROW($B$2:$I$9),32)))
Hi Jobowo
2 remarks.
A - The word robust
I'm sorry I was not clear. I'm under the impression that you think that I wrote that my formula is robust because I use a countif to check if there is a solution.
I'll try to clarify.
I did not use the word robust with its usual meaning as an English word, rather as a word from excel's technical jargon related to formulas, that we've been using in this board for some years (I believe it was introduced by Aladin).
We say a formula is robust when it survives changes in the location of the data, either as a result of a move or as a result of insertion/deletion of rows/columns.
To make it clear, consider these 2 cases
1 - after the formula is inserted
Test the following cases:
- insert a row above the table
- insert a column to the left of the table
- select the whole table and move it to another location
You can check that my formula is indifferent to any of these actions
2 - before the formula is inserted
Suppose a user sees this solution and wants to deploy it in another worksheet but where the data instead of being located in A1:H9 is in somewhere else. If you just replace in the formula the old locations with the new ones the formula will work without any changes.
This is what I meant with my formula being robust.
B - your solution
For what it's worth, my comment about the solutions.
Your solution does not address the general problem of this thread as stated in the title but that's also not what you wanted to post. You wanted to post a solution to the problem stated in by Mhenk and your solution works perfectly according to the specific data in this problem.
My formula and xld's address the more general problem of a reverse lookup in a table: given the value of an element in a table get it's row/column headers.
An experienced user will understand your formula and will use it and adapt it with no problem. I think a less experienced user will probably think the other formulas are easier to adapt to other cases, especially if the headers must be read in the header row/column.
Mhenk has several solutions that solve this problem. She/he will choose the one that works best for the specific case.
Cheers.
Last edited by pgc01; Dec 5th, 2009 at 09:38 PM.
Kind regards
PGC
To understand recursion, you must understand recursion.
Ah. Point well taken on the "robust" issue. I see that mine is not as robust as I had thought. Here is my solution...
or alternatively, from pgc01:=CHAR(MAX(IF($B$2:$I$9=$K$3,COLUMN($B:$I)+60-COLUMN($B:$B),32)))&"-"&CHAR(MAX(IF($B$2:$I$9=$K3,53-ROW($1:$8)+ROW($1:$1),32)))
I am not sure that any array solution can be made completely robust. In my experiments, moving the array down and right using insert rows/columns and then moving it back using range selection and drag-and-drop produced a net change in the formula. This can eventually break both of the formula above and--in my experiments--pgc01's first solution as well.=CHAR(MAX(IF($B$2:$I$9=$K3,COLUMN($B$2:$I$9)-COLUMN($B$2)+CODE("A")," ")))&"-"&CHAR(MAX(IF($B$2:$I$9=$K3,CODE("8")-ROW($B$2:$I$9)+ROW($B$2)," ")))
It seems that the problem is that Excel (2007 at least) uses different methods of formula adjustment that do not mirror each other. You can use one or the other with success but using one then the other results in a net change.
However this conclusion is tentative and needs more exhaustive experiments.
Like this thread? Share it with others