Find in which column(s) a value appear

[merlix3]

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

	A	B	C	D	E	...	SF
1	Date 1	Date 2	Date 3	Date 4	Date 5	Date ...	Date 500
2	Other	Other	Other	Other	Other	Other	Other
3	125487	245875	325665	125452	547854	325658	399856
4	302154	125475	245478	326585	214523	623524	222256
5	prog	362548	332145	214521	225325	325654	354789
...		autre	125624	235652	526352		299990
500			235478				236547

<tbody>
</tbody>

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

	M	N
45	Target Caller Id	Oldest Date
46		Date
47		Date
48		Date
49		Date
50		Newer Date
...		...

<tbody>
</tbody>

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