Excel: What column does this value appear in?
March 2, 2010 7:32 AM Subscribe
Excel: How can I find the column that a value appears in?
I have a NxM range of cells. All the cells are either unique or blank. Given a specified value, I want the column number, within that range, that the value appears in.
Basically, I want MATCH, but in 2 dimensions; MATCH only supports 1xN or Nx1 ranges.
I have a NxM range of cells. All the cells are either unique or blank. Given a specified value, I want the column number, within that range, that the value appears in.
Basically, I want MATCH, but in 2 dimensions; MATCH only supports 1xN or Nx1 ranges.
Not an elegant solution, but how about a separate MATCH formula in each row, with a separate formula to pick out the correct answer from the column of MATCHes?
You'd also need an ISNA function to weed out the non-matches.
Something like:
IF(ISNA(MATCH statement), 0, MATCH statement)
Then you can sum up all of the MATCHes since the non-matches have 0 as the result
posted by dabug at 9:28 AM on March 2, 2010
You'd also need an ISNA function to weed out the non-matches.
Something like:
IF(ISNA(MATCH statement), 0, MATCH statement)
Then you can sum up all of the MATCHes since the non-matches have 0 as the result
posted by dabug at 9:28 AM on March 2, 2010
I don't know how Vlookup would work in this scenario. That is for finding the value of a already known column in a series of rows based on the value in the first column. There is also Hlookup that is for (swap row and column in the vlookup description).
Is there a maximum number of columns you would be searching? You could cobble together some COUNTIF statements to say that if the COUNTIF for a certain column is >0, return that column. But you would have to write one for each column and nest them.
posted by soelo at 10:23 AM on March 2, 2010
Is there a maximum number of columns you would be searching? You could cobble together some COUNTIF statements to say that if the COUNTIF for a certain column is >0, return that column. But you would have to write one for each column and nest them.
posted by soelo at 10:23 AM on March 2, 2010
I think you want a lookup function (VLOOKUP? I get them confused when I am not actually in Excel brain-mode) wrapped in the COLUMN function
posted by misterbrandt at 11:37 AM on March 2, 2010
posted by misterbrandt at 11:37 AM on March 2, 2010
I think dabug is on the trail. vLookup is definitely not what you're looking for, as that will ONLY look in the leftmost column of an array.
Say you have a 3x3 array (_ represents a blank cell)
A__
_B_
__C
And you want to write a formula that will return "3" as the column number for "C". You need to do a MATCH on each row (1 through 3) combined with IFs and ISNAs as "rules." Essentially, the nested IFs and ISNAs will tell the formula to first look in Row 1 for "C" and return its column position. If it doesn't find it in Row 1 (as indicated by an #N/A), look in Row 2 for "C" and return its column position, etc.
=IF(ISNA(MATCH("C",1:1,0)),IF(ISNA(MATCH("C",2:2,0)),MATCH("C",3:3,0),MATCH("C",2:2,0)),MATCH("C",1:1,0))
Note that this will only work if you have unique values. It won't find more than one instance of "C", it will only find the "first" one (I.E. the one in the highest--lowest numbered--row)
I think that's what you're looking for. Granted if you have lots of rows it could get quite tedious nesting them, but I'm not aware of another way to do this. That, of course, doesn't mean it can't be done!
posted by jckll at 12:10 PM on March 2, 2010
Say you have a 3x3 array (_ represents a blank cell)
A__
_B_
__C
And you want to write a formula that will return "3" as the column number for "C". You need to do a MATCH on each row (1 through 3) combined with IFs and ISNAs as "rules." Essentially, the nested IFs and ISNAs will tell the formula to first look in Row 1 for "C" and return its column position. If it doesn't find it in Row 1 (as indicated by an #N/A), look in Row 2 for "C" and return its column position, etc.
=IF(ISNA(MATCH("C",1:1,0)),IF(ISNA(MATCH("C",2:2,0)),MATCH("C",3:3,0),MATCH("C",2:2,0)),MATCH("C",1:1,0))
Note that this will only work if you have unique values. It won't find more than one instance of "C", it will only find the "first" one (I.E. the one in the highest--lowest numbered--row)
I think that's what you're looking for. Granted if you have lots of rows it could get quite tedious nesting them, but I'm not aware of another way to do this. That, of course, doesn't mean it can't be done!
posted by jckll at 12:10 PM on March 2, 2010
Response by poster: Yeah, that chained ISNA-MATCH thing is what I'm currently doing; I was hoping for a better way.
Oh well.
posted by dmd at 12:23 PM on March 2, 2010
Oh well.
posted by dmd at 12:23 PM on March 2, 2010
I think this is what dabug was suggesting: http://dl.dropbox.com/u/1563425/Workbook1.xls
It seems to work perfectly without nesting IFs and MATCHes.
posted by Well that's a lie at 2:48 PM on March 2, 2010
It seems to work perfectly without nesting IFs and MATCHes.
posted by Well that's a lie at 2:48 PM on March 2, 2010
Response by poster: Unfortunately I need to be able to look up more than one thing at a time; dabug's solution only lets you look up one thing.
posted by dmd at 7:41 AM on March 3, 2010
posted by dmd at 7:41 AM on March 3, 2010
Response by poster: I DID IT!
The thing I want to match is in A6.
The table I'm matching against is in 'Do Not Touch'!G:G through L:L.
I used an array formula, where each element in the array is the result of MATCH on one of the columns, and where that result has been coerced into a boolean by ISNUMBER. Then I look for the TRUE, and get the offset of that.
=iferror(mid(offset('Do Not Touch'!$G$4,0,match(TRUE,{isnumber(match(A6,'Do Not Touch'!$G:$G,0)),isnumber(match(A6,'Do Not Touch'!$H:$H,0)),isnumber(match(A6,'Do Not Touch'!$I:$I,0)),isnumber(match(A6,'Do Not Touch'!$J:$J,0)),isnumber(match(A6,'Do Not Touch'!$K:$K,0)),isnumber(match(A6,'Do Not Touch'!$L:$L,0)),isnumber(match(A6,'Do Not Touch'!$M:$M,0))},0)-1),3,2),"-")
posted by dmd at 8:34 AM on March 3, 2010
The thing I want to match is in A6.
The table I'm matching against is in 'Do Not Touch'!G:G through L:L.
I used an array formula, where each element in the array is the result of MATCH on one of the columns, and where that result has been coerced into a boolean by ISNUMBER. Then I look for the TRUE, and get the offset of that.
=iferror(mid(offset('Do Not Touch'!$G$4,0,match(TRUE,{isnumber(match(A6,'Do Not Touch'!$G:$G,0)),isnumber(match(A6,'Do Not Touch'!$H:$H,0)),isnumber(match(A6,'Do Not Touch'!$I:$I,0)),isnumber(match(A6,'Do Not Touch'!$J:$J,0)),isnumber(match(A6,'Do Not Touch'!$K:$K,0)),isnumber(match(A6,'Do Not Touch'!$L:$L,0)),isnumber(match(A6,'Do Not Touch'!$M:$M,0))},0)-1),3,2),"-")
posted by dmd at 8:34 AM on March 3, 2010
This thread is closed to new comments.
The syntax for the VLookup function is:
VLookup( value, table_array, index_number, not_exact_match )
posted by MechEng at 8:16 AM on March 2, 2010