Formula to return a certain word if the value in one of the cells in the row occurs elsewhere in the list, but with a different period number

[diygail123]

Hi

Column B of my spreadsheet contains many occurrences of the period numbers 1 - 12, not always in numerical order.
Column G contains many occurrences of different journal numbers, in the format Jnnn.
I am looking for a formula in cell H1 onwards that returns the word "Reversal" if the Journal number in cell G1 occurs further down column G, but has a period number 1 less than that in cell B1.

For example, Cell B1 contains the period number 4. Cell G1 contains the text J123. If J123 occurs somewhere else in column G, and has a corresponding period number of 3 in column B, I would like a formula in Cell H1 that returns the word "Reversal".

I have a feeling this may need and array formula, and I am not too familiar with these, so any help would be very much appreciated.

Thanks

Gail

 

[JLGWhiz]

I am not that good at formulas, but try this and see if it does what you want.

=IF(OFFSET(INDEX(G2:G$999,MATCH(G1,G2:G$999,0)),0,-5)<$B$1,"Reversal","")

 

[DanteAmor]

Try this:

varios 10jul2020.xlsm
	B					G	H
1	4					J123	Reversal
2	1					j115
3	1					j116
4	1					j117
5	3					j118
6	3					j119
7	1					j120
8	2					j121
9	2					j122
10	3					j123
11	4					j124
12	4					j125
Hoja12

Cell Formulas
Range	Formula
H1	H1	=IF(COUNTIFS(B2:B12,B1-1,G2:G12,G1),"Reversal","")