Page 1 of 2 12 LastLast
Results 1 to 10 of 13

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. #1
    Board Regular mhenk's Avatar
    Join Date
    Jun 2005
    Location
    Milwaukee, WI
    Posts
    575

    Default 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. #2
    xld
    xld is offline
    Banned
    Join Date
    Feb 2003
    Location
    The Kingdom of Wessex
    Posts
    5,378

    Default 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. #3
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    14,991

    Default 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
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

  4. #4
    Board Regular
    Join Date
    Oct 2009
    Location
    Canada, EH
    Posts
    242

    Default 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. #5
    xld
    xld is offline
    Banned
    Join Date
    Feb 2003
    Location
    The Kingdom of Wessex
    Posts
    5,378

    Default Re: Reverse Index/Match?

    Quote Originally Posted by Jobowo View Post
    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. #6
    Board Regular
    Join Date
    Oct 2009
    Location
    Canada, EH
    Posts
    242

    Default 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.
    Last edited by Jobowo; Dec 5th, 2009 at 02:56 PM.

  7. #7
    Board Regular
    Join Date
    Oct 2009
    Location
    Canada, EH
    Posts
    242

    Default 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. #8
    xld
    xld is offline
    Banned
    Join Date
    Feb 2003
    Location
    The Kingdom of Wessex
    Posts
    5,378

    Default Re: Reverse Index/Match?

    Quote Originally Posted by Jobowo View Post
    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. #9
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    14,991

    Default Re: Reverse Index/Match?

    Quote Originally Posted by Jobowo View Post
    ... 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.

    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 08:38 PM.
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

  10. #10
    Board Regular
    Join Date
    Oct 2009
    Location
    Canada, EH
    Posts
    242

    Default 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 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com