find first instance, diplay value from another column

[ridgetown_rick]

In col A, I have the dates. I would like to create a formula in all the other columns in row 1 that will look fror the first instance of a value in each column and return the date that that threshold value is reached.

The value I am looking for is 3, but the values in the columns might look like 0 1 1 1 3 2 1 3 4 2 0 etc. I want to return the date of the FIRST instance of 3. How would I do this?
Thanks
Rick

 

[Barrie Davidson]

Have you tried VLOOKUP?

 

[ridgetown_rick]

I haven't used VLOOKUP for something like this before. I can't see how it would do what I need.

How would I return the date of the FIRST instance only?
I want this date to be displayed in a single cell at the top of the column.

Rick

 

[Aladin Akyurek]

I haven't used VLOOKUP for something like this before. I can't see how it would do what I need.

How would I return the date of the FIRST instance only?
I want this date to be displayed in a single cell at the top of the column.

Rick

Are you looking for something like this?

Book7
	A	B	C	D	E
1		21-12-03	17-2-03	13-2-03
2	Date	T1	T2	T3
3	13-2-03	1	2	3
4	17-6-03	0	1	3
5	17-7-03	1	1	1
6	16-10-03	1	0	0
7	17-2-03	1	3	0
8	27-11-03	1	3	3
9	19-4-03	0	1	1
10	23-6-03	2	3	2
11	19-12-03	0	2	2
12	21-12-03	3	3	2
13	9-8-03	0	3	2
14	13-6-03	2	3	3
15	15-9-03	1	1	2
16	2-11-03	2	2	3
17
Sheet1

The formula in B1 is...

=INDEX($A$3:$A$16,MATCH(3,B$3:B$16,0))

 

[ridgetown_rick]

That is sooooo perfect - you can't imagine the grin across my face right now.

Thank you!!!

 

[kenatu]

sorry i dont mean to hijack this thread, however i have need of similar expertise. the variation to my question is i want to look for the first instances of a particular value within groupings. for eg. i want to find the first time a person's monthly salary reached a set figure and return the date in a separate column.


Name Date Salary Result
John Doe 1/1/10 450 N/A
John Doe 2/1/10 500 2/1/10
john Doe 3/1/10 500 N/A
Mary Doe 11/1/09 380 N/A
Mary Doe 12/1/09 380 N/A
Mary Doe 1/1/10 500 1/1/10

would really appreciate the hep thanks

 

[Aladin Akyurek]

sorry i dont mean to hijack this thread, however i have need of similar expertise. the variation to my question is i want to look for the first instances of a particular value within groupings. for eg. i want to find the first time a person's monthly salary reached a set figure and return the date in a separate column.


Name Date Salary Result
John Doe 1/1/10 450 N/A
John Doe 2/1/10 500 2/1/10
john Doe 3/1/10 500 N/A
Mary Doe 11/1/09 380 N/A
Mary Doe 12/1/09 380 N/A
Mary Doe 1/1/10 500 1/1/10

would really appreciate the hep thanks

Is the max value the set figure?

 

[kenatu]

the excrcise is really to determine when a person has reached the top of their pay grade. so the set value would be the max value for group of individuals in the same scale.

 

[Aladin Akyurek]

the excrcise is really to determine when a person has reached the top of their pay grade. so the set value would be the max value for group of individuals in the same scale.

D2, control+shift+enter, not just enter, and copy down:

=IF(MATCH(MAX(IF($A$2:$A$7=A2,$C$2:$C$7)),
    IF($A$2:$A$7=A2,$C$2:$C$7),0)=ROW()-ROW($C$2)+1,$B2,"N/A")

 

[kenatu]

thank you for your very prompt response. I am tryin to get this to work however; i need to be able to search for the top of the salary scale as the figure may not be in the table. it is possible that nobody has reached the top of their pay scale so its not necessarily the thier most recent largest payment. could you tel me how to modify this to work like that? where i put the top in the code and then ask it to find the first instnaces of that inputted figure?

much appreciated.

Thanks