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

Lookup Value based on Date

Somendra Misra

Somendra Misra

Excel Ninja
Dear All,

In the attached file, I had a table for training schedule on sheet "Schdule". On another sheet "Sheet 3", I want to bring type of training in front of the dates for the whole year.

I had applied one formula, it is working for 2 consecutive days like for 27-May 2013 to 28-May 2013, but when I am extending the period lets say from 1-Jan-2013 to 5-Jan-2013, it is not working for whole period.

Kindly, help in correcting the formula or if a better way to do the same.

Regards,
Somendra.
 

Attachments

Hi Narayan,

Thanks for the reply, but if you see, type Safety is coming on every date for which schedule is not planned in the table.

Thanks, Somendra
 
=IF((B4<=Table1[To])*(B4>=Table1[From]),VLOOKUP(B4,Table1,5,1),"")

entered using ctrl+shift+enter seems to work (the "" in the formula are what it returns for dates not in the table)
 
Hi Somendra,

Try this too...
=IFERROR(INDEX(Table1[Type],MATCH(1,((Sheet3!B4>=Table1[From])*(Sheet3!B4<=Table1[To])),0)),"")

Confirm the formula with Ctrl + Shift + Enter, Not just Enter
 
Hi Jake....you formula works perfectly for 1st entry in the table but not for entries after that, it is calculating say for second entry in table the answer is second value of array but it is showing on 1st value that is blank.

Hi Xiq....your method works man...Thanks a lot...never thought of such an easy formula to do the same, if I compare with my original formula.
 
Hi all,

One more thing...I could not understand the logic of Match function in your foumula's. I mean lookup value '1', what will it do...does it had a special significance of using 1.
 
Hi
Somendra Misra said:
Hi all,

One more thing...I could not understand the logic of Match function in your foumula's. I mean lookup value '1', what will it do...does it had a special significance of using 1.
Click to expand...
Misra,
Yes, it does :)

We both (Debraj and me) do a check if the date is equal or higher/lower in our formula. This results in an array of TRUE's and FALSE's. There are a few ways these can be converted into ONE's and ZERO's. So e.g. something like {0,0,0,1,0,0}. If we ask to match ONE on this array, you get the result FOUR. This is then used by the INDEX function.

Does this make sense to you?
 
You must log in or register to reply here.
Back
Top