Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    find col and row

    Hi All,

    I'd like to be able to find if a name, say in cell A1, is in a range of cells, say B1:H10. If it is, return the col and row of the match; could be done as 2 separate formulas - 1 for the row and 1 for the col. Of course, if no match is found, both the col and row result should have something like "not found".

    No VBA - this is for Excel 2008 on the MAC.

    I have found lots of approaches but they all seem to have 1 thing in common: they only search a single row or single column. That's much easier. For example, MATCH will give an #N/A error if the range is not a single row or column.

    TIA

    Fred

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    If there's only one match, you can use:
    =IF(COUNTIF($B$1:$H$10,A1)>0,SUMPRODUCT(($B$1:$H$1 0=A1)*ROW($B$1:$H$10)),"")
    and
    =IF(COUNTIF($B$1:$H$10,A1)>0,SUMPRODUCT(($B$1:$H$1 0=A1)*COLUMN($B$1:$H$10)),"")
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Fred

    Another possible approach is to use a Dell

    zeddy

  4. #4
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    Zeddy - We're talking about a die-hard MAC user here.

    She and I were once talking about what we'd do if we hit it big in the lottery. I told her that I'd buy her a "real" computer - one that was Windows based. In fact, both a laptop and a desktop. She said she wouldn't use either one.

    Fred

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    Hi Rory,

    forgot to mention - there will only be 1 match in this case. Names are selected from a drop-down validation list. Once the name is selected, the list is recomputed. Yes - this does introduce a moment of hesitation in Excel (at least the way I did it) while Excel "computes" up to 100%. But she doesn't mind. It beats the old paper and pencil way she used to do and guarantees each name appears once and only once. There are about 150-200 names to pick from initially.

    Fred

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Quote Originally Posted by fburg View Post
    Zeddy - We're talking about a die-hard MAC user here.
    She and I were once talking about what we'd do if we hit it big in the lottery. I told her that I'd buy her a "real" computer - one that was Windows based. In fact, both a laptop and a desktop. She said she wouldn't use either one.
    Fred
    Fred,

    You mean like this?
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #7
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    Exactly RG

    So I labor under the burden of creating spreadsheets for Excel 2008 on the MAC (but I do it on my computer). Amazing what it can't do (as Rory once replied to once of my past posts).


  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Its one saving grace was that it made me feel good about having 2007 on my computer at work.
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Fred,

    This can also be done with match formulas. Change the value of A1 to match a value in the field. The row and column will be returned in cells N1 and N2. If there is no match, it will indicate "Not Found". This works only if there is one instance of the value.

    HTH,
    Maud

    fburg_match.png
    Attached Files Attached Files
    Last edited by Maudibe; 2015-01-22 at 07:59. Reason: Added conditional statement

  10. #10
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    ah, the old "hidden helper" column/row trick! Good one Maud!

    I already implemented Rory's approach but haven't finalized yet. I did notice a (further) slow-down of the sheet after doing that. So I may switch.

    I have found, at least in some cases, that helper columns/rows usually are faster than bigger formulas (see my other recent thread on "A Shorter Formula").

    Of course the other issue is that she's using Excel 2008 so no VBA (hence the constraint on the original request) - sorry Maud. I'm sure you could have come up with a great VBA solution!

    Fred

  11. #11
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Fred,

    It feels good to rotate the tires every once in a while

  12. #12
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    Maud, Rory

    I did a little playing around with Maud's solution. I certainly will not claim any originality in my approach.

    Don't know if it would work any faster but all the MATCH's in Maud's formulas caused some concern given this spreadsheet is starting to slow down as mentioned above with Rory's approach. Maybe it was Rory's SUMPRODUCT over a large array (the actual array is 40 cols by 10 rows, which I know isn't much). And the locations have to be found (or "not found") for about 250-350 entries - and that's both a row and a column. So the spreadsheet is examining 400 cells 500-700 times (for row and col for all the entries) with the SUMPRODUCT.

    Seems somewhat wasteful to examine 400 cells to find whether an item is in there once or not (as said, there's only 1 or no matches). Hence I wanted to examine Maud's solution some more. What I did, in Maud's sample spreadsheet - not my "production" version, for the rows:
    - for Row 1 in the hidden column K: =if(countif(B1:J1,$A$1)>0,ROW(),-1) but I also could have kept Maud's condition to be tested with my changes for the true and false parts; and then fill down
    - to get the row location: =if(max(K1:K10)>0, max(K1:K10), "not found")

    Column location would follow by extension.

    I also tried several variants of Rory's solution for the hidden col K - for example, for row 9: = (B9:J9=A1)*ROW(1:9)
    but that gave me either #VALUE or 0 depending on whether I confirmed as a regular formula or an array formula. Using SUMPRODUCT was not better. I think I'm missing something here.

    Thoughts?

    TIA

    Fred

  13. #13
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You need to add up the matching results, otherwise you'll just get the first one. You also need to use COLUMN not ROW:
    =SUMPRODUCT((B9:J9=$A$1)*(COLUMN(B9:J9)))
    for example, but MATCH with IFERROR would be more efficient, I suspect.
    Regards,
    Rory

    Microsoft MVP - Excel

  14. #14
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    Hi Rory,

    Not sure I'm following.

    First, my post #12 was aimed at getting the row of the match. I think I also suggested that "column location would follow by extension." In Maud's original, the values in col K said whether A1 was in that row; using column(B9:J9), per your post, gives the col that A1 appears in for the row if at all. Not that it matters since I could switch things around.

    Second, I had tried SUMPRODUCT with 2 arrays: one for the match (B9:J9=A1) and one to generate the row or column #. This always gave zero. I thought SUMPRODUCT was supposed to multiply the corresponding element of each array, sum them together and return one number which is the sum. So I didn't need to use * between the 2 arrays. What I was trying to do was multiply the TRUE/FALSE (1/0) of the first array by the corresponding row #s (which maybe I didn't do correctly); adding all the products would result in 0 if not found or the row# multiplied by 1 for the row where found.

    So I'm a little confused.

    Fred

  15. #15
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 651 Times in 593 Posts
    Fred,

    FYI.... As you are probably aware, if you decide to keep the Match formulas, they can be moved off the current sheet and just reference the data from the another sheet. Contrary to belief, this would not slow the calculations doing this since the cell values are just basically memory locations.

    Maud

Page 1 of 2 12 LastLast

Posting Permissions

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