• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need Help with a Formula that uses VLOOKUP

A

AnilR

New Member
Hi,

In the attached Sample Sheet, I'd like to be able to VLOOKUP in Cell C7 the Period Factor in Cells I5 through I50 based on Today's date in Cell E4. So, for next year's December 31st, I'd like to be able to see 26.5 in Cell C7. In the Sample Sheet, I've hard-coded the Date of Cell C5 into my VLOOKUP formula in Cell C7. I can't seem to be able to figure out how to increment Cell C5 when Cell E4 changes to a new December 31st in subsequent years.

Any help is appreciated. Please if you can, make changes to the attached Sample Sheet and re-post with your solution for my download.

Thanks
 

Attachments

  • Sample Sheet.xlsx
    11.9 KB · Views: 4
ok you can to Use This
Code: 
=SUMIFS($I$5:$I$50,$H$5:$H$50,$C$5,$G$5:$G$50,$B$6)
 

Attachments

  • Sample Sheet1.xlsx
    11.7 KB · Views: 6
Hi herofox:
It doesn't work. My only input to this sheet is what is in Cell C3 --the Birth date. This birth date can be any birth date. Your input of 70.5 in Cell B6 is not a valid input, although it works with your SUMIFS formula in Cell C7. I can't see how it will work for 12/31/2020 and subsequent years.

Please re-visit Cell E4 and see if you can come up with some thing the will increment Cell C5 when the next year comes along.

Thanks.
 
what the problem with you ?!!!
i think you didn't see my attached every result is correct as you asked before
try to change any data !!!
 
You are right. I changed the Birth date to say, 10/19/1949 and the result is 27.4. However, I may have defined what I wanted incorrectly.

What I want is that for any Birth Date I input and for any December 31 date, subsequently and including what is currently shown in Cell E4, should produce the appropriate "Period Factor" from I5 thru I50 in accordance with the value in Cell C5. So if Cell C5 is 12/31/2023, the "Period Factor" is 27.4 and if Cell C5 changes to 12/31/2024, the "Period Factor" is 26.5. and so on and so on.

I'm thinking that Cell C5 has to change when Cell E4 changes. This way when the next year comes along Cell C5 will be incremented and as a result the VLOOKUP formula I had in Cell C7 will choose the matching "Period Factor" from I5 thru I50

I'm open to a new formula to what is in Cell C7 --the VLOOKUP
 
AnilR said:
You are right. I changed the Birth date to say, 10/19/1949 and the result is 27.4. However, I may have defined what I wanted incorrectly.

What I want is that for any Birth Date I input and for any December 31 date, subsequently and including what is currently shown in Cell E4, should produce the appropriate "Period Factor" from I5 thru I50 in accordance with the value in Cell C5. So if Cell C5 is 12/31/2023, the "Period Factor" is 27.4 and if Cell C5 changes to 12/31/2024, the "Period Factor" is 26.5. and so on and so on.

I'm thinking that Cell C5 has to change when Cell E4 changes. This way when the next year comes along Cell C5 will be incremented and as a result the VLOOKUP formula I had in Cell C7 will choose the matching "Period Factor" from I5 thru I50

I'm open to a new formula to what is in Cell C7 --the VLOOKUP
Click to expand...
If cell C5 shall change based on the changes of E4 then you should appropriately define the relationship. Currently, as it stands, you have defined it based on C4 which will always result in 27.4.
 
I'll have to work on this some more. I'm not doing a good job at defining what it is I wanted. "My bad"
Both of you are correct in your interpretations based on what I asked for.
Thanks responses.
 
You must log in or register to reply here.
Back
Top