Extension to IF formula needed

[saraapple]

I have a simple IF formula as follows:
=IF(B1=265,(Sheet4!A1),(""))

What I would like to do is for each time it finds "265" in column B I want it to return (Sheet4!A1) then (Sheet4!A2) then (Sheet4!A3) etc. I have tried various $ and it will not work, I am thinking that I perhaps need to include an INDEX?
Any help gratefully appreciated.

 

[mohadin]

Hi
What about

=IF(B4=265,"Sheet4!A"&COUNTIF($B$1:$B4,B4),"")

 

[StephenCrump]

Not totally clear, but do you mean something like this:

	A	B	C	D
1		265
2				P
3		265		R
4				W
5
6
7
8		265
Sheet3

Cell Formulas
Range	Formula
D2:D4	D2	=IFERROR(INDEX(Sheet4!A$1:A$10,SMALL(IF(B$1:B$10=265,ROW(B$1:B$10)-ROW(B$1)+1),ROWS(D$2:D2))),"")

	A
1	P
2	Q
3	R
4	S
5	T
6	U
7	V
8	W
9	X
10	Y
Sheet4

 

[saraapple]

Apologies I have not been clear on what I need!
The first suggestion from @[U]mohadin[/U] almost works but I want it to return the contents of the cell in sheet4 cell A1 - this formula returns the text "Sheet4A1" then for the next 265 it returns "Sheet4!A2". How can I make it return the value of these cells?

 

[mohadin]

Then try

=IF(B4=265,INDIRECT("Sheet4!A"&COUNTIF($B$1:$B4,B4)),"")

 

[StephenCrump]

Similar, but without the volatile INDIRECT ...

	A	B	C	D
1		265		P
2
3		265		Q
4
5
6
7
8		265		R
9
10
Sheet3

Cell Formulas
Range	Formula
D1:D10	D1	=IF(B1=265,INDEX(Sheet4!A:A,COUNTIF(B$1:B1,265)),"")

	A
1	P
2	Q
3	R
4	S
5	T
6	U
7	V
8	W
9	X
10	Y
Sheet4