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:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]REF#[/TD]
[TD]1/4/13[/TD]
[TD]12/4/12[/TD]
[TD]7/18/13[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]REF#
[/TD]
[TD]1/4/13[/TD]
[TD]12/4/12[/TD]
[TD]7/18/13[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Ref#[/TD]
[TD]2/4/12[/TD]
[TD]7/2/13[/TD]
[TD]8/12/13[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Ref#[/TD]
[TD]2/4/12[/TD]
[TD]7/2/13[/TD]
[TD]8/12/13[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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.