Return column number of specific value
Posted by Zif on August 09, 2001 2:46 AM
I recently read a message on this board regarding returning the column number of the maximum value in a range of cells.
What I need is slightly different. I have (for example) a range of cells C1:J1 which will contain the text value "No", but one (and only one) cell will contain the text value "Yes".
What I need os a formula to return the column number of the cell containing the value "Yes"
Suggestions??
Posted by Aladin Akyurek on August 09, 2001 3:32 AM
If it is guaranteed that there is a single Yes in C1:J1, then
=SUMPRODUCT((C1:J1="Yes")*(COLUMN(C1:J1)))
will give you the column number of that Yes value.
If you'd want a check before applying the above computation, use
=IF(COUNTIF(C1:J1,"Yes")=1,SUMPRODUCT((C1:J1="Yes")*(COLUMN(C1:J1))),"Error: Too many Yes values")
Also, if you want to have the address of the Yes cell, use
=ADDRESS(1,SUMPRODUCT((C1:J1="Yes")*(COLUMN(C1:J1))))
or (includes the check)
=IF(COUNTIF(C1:J1,"Yes")=1,ADDRESS(1,SUMPRODUCT((C1:J1="Yes")*(COLUMN(C1:J1)))),"Error: Too many Yes values")
Aladin
==========
Posted by Joe Was on August 09, 2001 3:49 PM
Re: Return full address
=ADDRESS(1,MATCH("Yes",A1:G1,0))
the 1 before the MATCH is the ROW, if C1 is the Yes, then the above gives $C$1.
=LEFT(ADDRESS(COLUMN(A1:A30,1),3) & MATCH("Yes",A1:A30,0)
If A21 is the Yes then the above gives $A$21.
To work right your formula range needs to start in A for the column formula (the first one) and the range needs to start in 1 for the row formula (the second one) even if you do not have data in some of the cells. JSW
