Results 1 to 15 of 19
Thread: find col and row

20150121, 00:10 #1
 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

20150121, 02:19 #2
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,272
 Thanks
 3
 Thanked 187 Times in 173 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

20150121, 05:36 #3
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 2,762
 Thanks
 132
 Thanked 466 Times in 444 Posts
Hi Fred
Another possible approach is to use a Dell
zeddy

20150121, 07:36 #4
 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 diehard 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

20150121, 07:40 #5
 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 dropdown 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 150200 names to pick from initially.
Fred

20150121, 07:46 #6
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 9,422
 Thanks
 368
 Thanked 1,455 Times in 1,324 Posts
Fred,
You mean like this?May the Forces of good computing be with you!
RG
PowerShell & VBA Rule!
My Systems: Desktop Specs
Laptop Specs

20150121, 07:55 #7
 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).

20150121, 08:16 #8
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,272
 Thanks
 3
 Thanked 187 Times in 173 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

20150121, 22:58 #9
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,561
 Thanks
 111
 Thanked 621 Times in 566 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.pngLast edited by Maudibe; 20150122 at 06:59. Reason: Added conditional statement

20150122, 09:16 #10
 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) slowdown 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

20150122, 10:14 #11
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,561
 Thanks
 111
 Thanked 621 Times in 566 Posts
Fred,
It feels good to rotate the tires every once in a while

20150122, 11:58 #12
 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 250350 entries  and that's both a row and a column. So the spreadsheet is examining 400 cells 500700 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

20150123, 11:09 #13
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,272
 Thanks
 3
 Thanked 187 Times in 173 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

20150123, 12:23 #14
 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

20150123, 21:34 #15
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,561
 Thanks
 111
 Thanked 621 Times in 566 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