Find previous occurance

[simonwait]

Hi I have a list of string values in column D on a sheet. I need to get a formula in column J which looks at the string in Column D and then searches back up column D to find the same string. Once found it should return the value on that row of column F. If it doesn't find anything then just enter a 0. If tried match, index but maybe wrongly. I really dont want to use VBA as this will be sent around the office and this then causes problems with others setups

[Tony Valko]

Can you post some sample data and tell us what result you expect?

[simonwait]

Yeah

D F J
1 Cat 1000 0
2 Cat 20 1000
3 Dog 15 0
4 Dog 10000 15
5 Cat 500 20
6 Dog 1 10000

So Column J takes the string from column D and then searches for the previous instance. it then looks at the value in column F of that instance and returns it

Does that make sense????

[simonwait]

Sorry formatting want odd. Will try to make it clearer

[simonwait]

Capture.JPG

Here this is better

[Tony Valko]

Try this...

Data Range

	D	F	J
1
2	Cat	1000	0
3	Cat	20	1000
4	Dog	15	0
5	Dog	10000	15
6	Cat	500	20
7	Dog	1	10000
8	------	------	------

This array formula** entered in J2 and copied down:

=IF(MATCH(D2,D:D,0)=ROW(),0,INDEX(F:F,SMALL(IF(D$2:D$7=D2,ROW(D$2:D$7)),COUNTIF(D$2:D2,D2)-1)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[Phuocam]

Try this in J2:

=IF(COUNTIF($D$1:D1,D2)=0,0,LOOKUP(2,1/($D$1:D1=D2),$F$1:F1))

[FlameRetired]

Another way. In J2 (this cannot start in row 1). Array entered also.

Please Login or Register  to view this content.

Row\Col	D	F	J
1
2	Cat	1000	0
3	Cat	500	1000
4	Cat	365	500
5	Dog	456	0
6	Dog	987	456
7	Cat	123	365
8	Dog	951	987