Find in which column(s) a value appear

[merlix3]

So I have this table that looks like this in a sheet called Raw Data:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: right"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]SF[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD="align: center"]Date 1[/TD]
[TD="align: center"]Date 2[/TD]
[TD="align: center"]Date 3[/TD]
[TD="align: center"]Date 4[/TD]
[TD="align: center"]Date 5[/TD]
[TD="align: center"]Date ...[/TD]
[TD="align: center"]Date 500[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: center"]Other[/TD]
[TD="align: center"]Other[/TD]
[TD="align: center"]Other[/TD]
[TD="align: center"]Other[/TD]
[TD="align: center"]Other[/TD]
[TD="align: center"]Other[/TD]
[TD="align: center"]Other[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD="align: right"]125487[/TD]
[TD="align: right"]245875[/TD]
[TD="align: right"]325665[/TD]
[TD="align: right"]125452[/TD]
[TD="align: right"]547854[/TD]
[TD="align: right"]325658[/TD]
[TD="align: right"]399856[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD="align: right"]302154[/TD]
[TD="align: right"]125475[/TD]
[TD="align: right"]245478[/TD]
[TD="align: right"]326585[/TD]
[TD="align: right"]214523[/TD]
[TD="align: right"]623524[/TD]
[TD="align: right"]222256[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]prog[/TD]
[TD="align: right"]362548[/TD]
[TD="align: right"]332145[/TD]
[TD="align: right"]214521[/TD]
[TD="align: right"]225325[/TD]
[TD="align: right"]325654[/TD]
[TD="align: right"]354789[/TD]
[/TR]
[TR]
[TD]...[/TD]
[TD="align: right"][/TD]
[TD="align: right"]autre[/TD]
[TD="align: right"]125624[/TD]
[TD="align: right"]235652[/TD]
[TD="align: right"]526352[/TD]
[TD="align: right"][/TD]
[TD="align: right"]299990[/TD]
[/TR]
[TR]
[TD]500[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]235478[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]236547[/TD]
[/TR]
</tbody>[/TABLE]

So as you can see the table goes (or will eventually go) from A1 to SF500 (a matrix named All in my spreadsheet). These represent caller IDs (the "prog" and "autre" represent some kind of errors but we don't really care about them). I would Like a tool that would allow me to find at what dates a specific caller ID called.

So it would look like this (or something similar) in an other sheet called Stats Data

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]N[/TD]
[/TR]
[TR]
[TD]45[/TD]
[TD]Target Caller Id[/TD]
[TD]Oldest Date[/TD]
[/TR]
[TR]
[TD]46[/TD]
[TD][/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]47[/TD]
[TD][/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]48[/TD]
[TD][/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]49[/TD]
[TD][/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]50[/TD]
[TD][/TD]
[TD]Newer Date[/TD]
[/TR]
[TR]
[TD]...[/TD]
[TD][/TD]
[TD]...[/TD]
[/TR]
</tbody>[/TABLE]

So here basically one would enter in M40 the Target Id and in N40 and below would appear the dates at which that ID called. I though we could do this by finding the target and returning to the value in the first row of the same column but it's the multiple value that causes problem. Not all ID will be there multiple times but some will (the most frequent one is there 16 times so up to 25 iterations should be correct).

I tried before coming here to lookup a value (that I knew to be unique, one step at the time) and using the column reference as a reference for an index value as a matrix function but it failed... Here is what I tried, written in cell N45:

{=INDEX(All;MAX(SI(A3:SF500=M45;COLONNE(A3:SF500)-COLONNE(A1)+1)))}

Thanks in advance for the help, It will truly help

 

[easy2understandexcel]

Hi.
1. Insert a row above row 1 in your raw data.
2. Put a formula: =if(COUNTIF(A3:A500, 'Stats Data'!$M$45)>0,column(),"") , and drag it from A1 to SF1
3. On the Stats Data sheet use the small() function combined with hlookup() to find the date value of each column that the phone number appeared in (each column that it appears in will have a number at the top of the column)

Check out my youtube channel please --> Excel 101