Find a column reference for a cell meeting 2-dimensional criteria

[crazytalker90]

Hello! I have been trying to do this for many days now, and so finally decided to join this forum and ask for help.

I am trying to get the column number of a cell that meets both vertical and horizontal criteria within a matrix.

Specifically I am trying to produce a formula that will give me the column number of a column that both includes the number 1 and has a specific REF #

Example:

REF#	1/4/13	12/4/12	7/18/13
1	1
2			1
3		1

<tbody>
</tbody>

e.g. For an input of the REF# 3, I would like it to output 3 (3rd column is where REF# 3 has a 1 in the matrix)


The reason I am looking to do this is to produce an OFFSET function that will utilize this column number to give me the heading title (Date) for different REF #s corresponding to where the number 1 is located.

Any tips will help! I've tried using combinations of IF, OR, AND, SUMPRODUCT, VLOOKUP, HLOOKUP, OFFSET, INDEX, COLUMN, and MATCH but can't seem to quite figure it out

~Charlie

 

[Aladin Akyurek]

Hello! I have been trying to do this for many days now, and so finally decided to join this forum and ask for help.

I am trying to get the column number of a cell that meets both vertical and horizontal criteria within a matrix.

Specifically I am trying to produce a formula that will give me the column number of a column that both includes the number 1 and has a specific REF #

Example:

REF#	1/4/13	12/4/12	7/18/13
1	1
2			1
3		1

<tbody>
</tbody>

e.g. For an input of the REF# 3, I would like it to output 3 (3rd column is where REF# 3 has a 1 in the matrix)


The reason I am looking to do this is to produce an OFFSET function that will utilize this column number to give me the heading title (Date) for different REF #s corresponding to where the number 1 is located.

Any tips will help! I've tried using combinations of IF, OR, AND, SUMPRODUCT, VLOOKUP, HLOOKUP, OFFSET, INDEX, COLUMN, and MATCH but can't seem to quite figure it out

~Charlie

Let A:D house the above sample.

Let F2 house 3, a REF # of interest.

G2, just enter:

=MATCH(1,INDEX($B$2:$D$4,MATCH($F2,$A$2:$A$4,0),0),0)

If you want the date in the headers row...

=INDEX($B$1:$D$1,MATCH(1,INDEX($B$2:$D$4,MATCH($F2,$A$2:$A$4,0),0),0))

 

[crazytalker90]

Let A:D house the above sample.

Let F2 house 3, a REF # of interest.

G2, just enter:

=MATCH(1,INDEX($B$2:$D$4,MATCH($F2,$A$2:$A$4,0),0),0)

If you want the date in the headers row...

=INDEX($B$1:$D$1,MATCH(1,INDEX($B$2:$D$4,MATCH($F2,$A$2:$A$4,0),0),0))

Wow! Very impressive @Aladin! Such a nice, simple solution. Thanks so much. To make it a little tougher, is there a way to make the formula to work in an example like this:

Ref#	2/4/12	7/2/13	8/12/13
1	0
1	1
2			1
3		1

<tbody>
</tbody>

So that for in a case like with REF# 1 it still finds the REF# 1 and 1 in the matrix match despite the first REF# 1 where a 1 is not present?

~Charlie

 

[Aladin Akyurek]

Wow! Very impressive @Aladin! Such a nice, simple solution. Thanks so much.

You are welcome.

To make it a little tougher, is there a way to make the formula to work in an example like this:

Ref#	2/4/12	7/2/13	8/12/13
1	0
1	1
2			1
3		1

<tbody>
</tbody>

So that for in a case like with REF# 1 it still finds the REF# 1 and 1 in the matrix match despite the first REF# 1 where a 1 is not present?

~Charlie

Duplicate records change the nature of the task:

G2, control+shift+enter, not just enter:

=MIN(IF(IF($A$2:$A$5=$F2,$B$2:$D$5)=1,$B$1:$D$1))

 

[crazytalker90]

Wow! Thanks a lot! That's so much more elegant than the way I was trying to build the formula:

OFFSET(INDEX(A:A,MATCH(F2,A:A)),-ROW(INDEX(A:A,MATCH(F2,A:A)))+6, [here's where I was having trouble finishing it] )

Yours is much better. Would you be able to explain to me how exactly your formula works? I got lost at the "=1"

~Charlie

 

[Aladin Akyurek]

Wow! Thanks a lot! That's so much more elegant than the way I was trying to build the formula:

OFFSET(INDEX(A:A,MATCH(F2,A:A)),-ROW(INDEX(A:A,MATCH(F2,A:A)))+6, [here's where I was having trouble finishing it] )

Yours is much better.

You are welcome.

Would you be able to explain to me how exactly your formula works? I got lost at the "=1"

~Charlie

The inner IF produces a result array (let's call it INNER) consisting of values corresponding to F2 ( the REF # of interest) and FALSE values. The outer IF produces a result array (let's call OUTER) consisting of header dates corresponding to the data value of 1 and FALSE values. MIN, run on the OUTER array result, picks out the smallest date value from that array as the end result.

 

[crazytalker90]

Great, thanks so much Aladin! Very powerful usage of IF, excited to explore that more. Have a great evening!

~Charlie

 

[Aladin Akyurek]

Great, thanks so much Aladin! Very powerful usage of IF, excited to explore that more. Have a great evening!

~Charlie

You too. Thanks for providing feedback.