• 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.

Help with Formula - Continuous Lookup of Value based on Year

See attached test data.

I need help with a formula that will pull the value from the data set on the right based on the the text in cell B1 BUT, if the value is ZERO, then I want it to pull from the next year, and if that value is zero, then I want to pull the next year.

Based on the text in B2, If I am looking for 17LP data, it would pull 17LP but if there is 0, then it will pull 18LP. In addition, if I change the year in B2 to 18LP, it will only pull from 18LP unless there is a 0, then it will go to 19LP.

Thanks in advance!

-Hunter
 

Attachments

  • Test Data.xlsx
    9.2 KB · Views: 6
Last edited:
Childress Hunter

Check if this way suits your project

Decio
 

Attachments

  • Test Data Decio.xlsx
    12.2 KB · Views: 8
Hi Decio,

Interesting method, is there a way to do it without the control unit? This formula will be in the middle of a spreadsheet so ideally, I would like to type "17 LP" or "18 LP" or "19 LP" in B1 and it would pull the values.

Also, it doesn't appear to be pulling the values correctly. For example, when I open it up on 19 LP, it should be showing all the values in 19 LP but it's just showing the 30.25 down the column which is incorrect.

Do you think it's doable?
 
Back
Top