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:

[TABLE="class: grid, width: 500"]
<tbody>[TR="bgcolor: #efebe6"]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[TH]D[/TH]
[TH]E[/TH]
[TH]F[/TH]
[TH]G[/TH]
[TH]H
[/TH]
[TH]I
[/TH]
[TH]J
[/TH]
[TH][/TH]
[TH]K
[/TH]
[TH]L
[/TH]
[TH]M
[/TH]
[TH]N
[/TH]
[TH][/TH]
[TH][/TH]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD]
[/TD]
[TD="align: right"]Tim
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]Jane
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD][/TD]
[TD="align: right"]1st SignOff
[/TD]
[TD="align: right"]2nd SignOff
[/TD]
[TD="align: right"]3rd SignOff
[/TD]
[TD="align: center"]
[/TD]
[TD="align: right"]Sch. From
[/TD]
[TD="align: right"]Sch. To
[/TD]
[TD="align: right"]Actual From
[/TD]
[TD="align: right"]Actual To
[/TD]
[TD="align: right"]Sch Activity
[/TD]
[TD="align: right"]Activity
[/TD]
[TD="align: right"]Sch. From
[/TD]
[TD="align: right"]Sch. To
[/TD]
[TD="align: right"]Actual From
[/TD]
[TD="align: right"]Actual To
[/TD]
[TD="align: right"]Sch Activity
[/TD]
[TD="align: right"]Activity
[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]Tim
[/TD]
[TD="align: right"]=H4
[/TD]
[TD="align: right"]=H6
[/TD]
[TD="align: right"]=H8
[/TD]
[TD="align: center"]
[/TD]
[TD="align: right"]1:45
[/TD]
[TD="align: right"]3:15
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]Open
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]11:00
[/TD]
[TD="align: right"]12:21
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]Open
[/TD]
[TD="align: right"]
[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]Jane
[/TD]
[TD="align: right"]=H4
[/TD]
[TD="align: right"]=H6
[/TD]
[TD="align: right"]=H8
[/TD]
[TD="align: center"]
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3:53
[/TD]
[TD="align: right"]4:07
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]SignOff
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1:08
[/TD]
[TD="align: right"]1:23
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]SignOff
[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]Holly
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]3:30
[/TD]
[TD="align: right"]5:00
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]Open
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]12:45
[/TD]
[TD="align: right"]2:15
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]Open
[/TD]
[TD="align: right"]
[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]Bart
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]6:58
[/TD]
[TD="align: right"]7:26
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]SignOff
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4:31
[/TD]
[TD="align: right"]5:01
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]SignOff
[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD]John
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]7:15
[/TD]
[TD="align: right"]9:45
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]Open
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]4:30
[/TD]
[TD="align: right"]6:35
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]Open
[/TD]
[TD="align: right"]
[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD]Peter
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]8:30
[/TD]
[TD="align: right"]8:44
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]SignOff
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]5:56
[/TD]
[TD="align: right"]6:10
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]SignOff
[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: right"]Robert
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]6:35
[/TD]
[TD="align: right"]6:48
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]SignOff
[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]


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