MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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