VLOOKUP gives Mixed Results

Need help with setting up two main formulas. First one is for cells I11 to I27 and then repeated at K11 to K27, M11 to M27, O11 to O27, etc all the way up to AY11 to AY27. The present formula I am using works only part of the time and I think there must be some type of syntax error. The formula is as follows =IF(ISNA(VLOOKUP(I$39,$L9:$S9,2,FALSE)),"",VLOOKUP(I$39,$L9:$S9,2,FALSE))

First, it is suppose to check cell I39 for the year and go to the Lookup table. I have tried VLOOKUP, HLOOKUP, MATCH, LOOKUP, etc but all have the same problem……they get some cell values correct but not all . Using VLOOKUP I wanted to check the date and if it matches then transfer the value from the next cell to the right (position2) to the matching cells below. If no match , then no entry. The problem starts because many of the cells do not get transferred. I believe it is a result of the way I have written the formula…..the problems get worse after year 2020 (column S)

You will note that the interval time cycle for each event is different …..maybe yearly all the way to every 20 years so there is a wide range of values. It seems like the formula is looking for only a match with the first desired value. Example if you open the spread sheet , you will note that many cells are highlighted with “yellow”. These are cells where the formula did not work. At cell W42 , the formula should have gone to the lookup table and looked for 2022 and since there is a 2022 in cell N10 , should have then transferred the value of -4009 into cell W42.

The second formula does not exist but you can see the logic on the spreadsheet. Col. H cells H41 to H56 need to look to the right and find the first intersecting value. For cell H11 it should look down row 11 and then see -4026 at cell Q11. Then it should take that value (-4026) and divide it by the number of years shown in column F, cells F11 to F26. And then take that value and put it in all of the cells preceding the -4026. So the value of -805 should be shown in cells J11 - L11 – N11 - P11

I put a second set of completed cells below the "problem area" to show what the completed document should look like.....done manually.

Looking for any help I can get

Thanks

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

as Glenn noted posting a sample will go a long way toward getting the appropriate answer.
However from what I see in this formula =IF(ISNA(VLOOKUP(I$39,$L9:$S9,2,FALSE)),"",VLOOKUP(I$39,$L9:$S9,2,FALSE)) is that you are saying to look at the value in I39 and go to see if it is in L9 and if it is go over to M9 (the 2 part of the formula) and return that value. I don't know why you have it going from L9:S9 since anything past M9 is irrelevant to the formula - just a thought.

shouldnt it be
=VLOOKUP(I$39,$L9:$S24,2,FALSE)

In my experience lookup can work when values are in ascending order and not so good when the values are out of sequence. Vlookups on the other hand don't float looking for their values. If you tell it to start looking in cell L9 it will look for the in L9 and if it is in M9 instead it will not find it. Have you tried Martin's formula?