Automatacally Select a Julian Date

[johnnyb5]

I’ve developed a formula which calculates values base on Julian Dates. The formula is working fine except I must manually change the Julian date cell reference monthly. I’d like some help to find a way to automatically select the correct Julian date reference from my list.

The formula is:
=IF(VALUE(RIGHT($D2,"3"))<'REP DAY'!$A$9, VALUE(RIGHT($G2,"3"))-'REP DAY'!$A$9, VALUE(RIGHT($G2,"3"))-VALUE(RIGHT($D2,"3")))

The Julian date reference is: 'REP DAY'!$A$9

On the REP DAY Sheet is a header in A1, and 12 monthly dates in A2: A13; 16, 47, 75, 106, 136, 167, 197, 228, 259, 289, 320, 350. This list lacks the two digit year prefix (so that I can use it from year to year).

Last month I used the cell reference A8 and next month I will change it again to A10, currently I’m using A9 as shown in the formula.

The variable to determine which date to use is the current Julian date listed in Column G of the current active sheet where the formula is located. So if the current Julian date in cell G2 was 15240 I want it to select the equal value or closest smaller date. Therefore 15240 would select 228.

Does anyone know how to select a value equal to or the next smaller one in a list than the Julian Date listed in G2?

 

[johnnyb5]

Thanks for taking the time to update me Rick. Alpha Frog did mention that but it took me a few minutes to get that he was referring to the forum and not my formula use in excel. I'll be more conscience of the less than symbol in posts from now on. The tips are great!!
JB