Reverse Index/Match?

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 ...

1. Reverse Index/Match?

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?

2. Re: Reverse Index/Match?

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))))

3. Re: Reverse Index/Match?

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

ABCDEFGHIJKLM
1 ABCDEFGH
21Q1        PieceLocation
32  T2      Q1A-1
43         T2C-2
54         R1
65         K2F-7
76
87     K2
98
10
[Book1]Sheet2

4. Re: Reverse Index/Match?

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)))

5. Re: Reverse Index/Match?

Originally Posted by Jobowo
xld's formula can be modified a little...
Not necessary mate, he has the lookup values in the table, as pgc01's example demonstrates.

6. Re: Reverse Index/Match?

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.

7. Re: Reverse Index/Match?

...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)))

8. Re: Reverse Index/Match?

Originally Posted by Jobowo
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.
So you avoiud a lookup by doing a string construction. What is the point of that?

9. Re: Reverse Index/Match?

Originally Posted by Jobowo
... and to thus sidestep the separate "robustness" check. ...
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.

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.

10. Re: Reverse Index/Match?

Ah. Point well taken on the "robust" issue. I see that mine is not as robust as I had thought. Here is my solution...

=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)))
or alternatively, from pgc01:

=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)," ")))
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.

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.

Page 1 of 2 12 Last

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•