Excel Formula

[chrono2483]

Is there such a formula, that will search a column for a particular word or value - find the first occurance of it, then incorporate the offset formula to return values 3 cells to its left? Then, in another cell, a similar formula to look for the same work/value in the same column, but skip the first one, and return the values 3 cells to its left? Then again in another cell, a formula that would skip the first 2 occurances of the word/value and return the values 3 cells to its left?

in total, 3 formulas - skipping the word/value of the one before it.

 

[cyrilbrd]

Hi, like this?

Excel 2013
	A	B	C	D	E	F	G	H
1	word/value	other_1	other_2		result
2	this	2.551	2.519		this	2.519	3.527	4.581
3	this	3.386	3.527		that	7.418	8.207	10.749
4	this	4.708	4.581		2	5.102	6.733	9.57
5	2	5.866	5.102
6	2	6.307	6.733
7	that	7.600	7.418
8	that	8.406	8.207
9	2	9.709	9.570
10	that	10.937	10.749
chrono2483

Cell Formulas
Range	Formula
F2	{=IF(COLUMNS($F2:F2)>COUNTIF($A:$A,$E2),"",INDEX($C:$C,SMALL(IF($A$2:$A$10=$E2,ROW(A$2:A$10)),COLUMNS($F2:F2))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[chrono2483]

Hi Cyrilbrd,

My Sheet looks more like this:

	A	B	C	D	E	F	G	H	I	J		K	L	M	N
1						Tim						Jane
2		1st SignOff	2nd SignOff	3rd SignOff		Sch. From	Sch. To	Actual From	Actual To	Sch Activity	Activity	Sch. From	Sch. To	Actual From	Actual To	Sch Activity	Activity
3	Tim	=H4	=H6	=H8		1:45	3:15			Open		11:00	12:21			Open
4	Jane	=H4	=H6	=H8				3:53	4:07		SignOff			1:08	1:23		SignOff
5	Holly					3:30	5:00			Open		12:45	2:15			Open
6	Bart							6:58	7:26		SignOff			4:31	5:01		SignOff
7	John					7:15	9:45			Open		4:30	6:35			Open
8	Peter							8:30	8:44		SignOff			5:56	6:10		SignOff
9	Robert													6:35	6:48		SignOff
10

<tbody>
</tbody>

I am intending to use the formulas in range B3:D9. The rows of data from Col F onwards may vary. While the columns will be in the same place/order, the data of rows can/will change. I want to be able to recognize the name in Col A, and search F1:N1 for the name. Once matched, search that range of data for the required info for B3:D9.

Thank you