I am try to come up with a formula that will look up the start (B1) and end date (B2) and return a cost value found in D12 to D14.
Currently B6, B7, B8 are used to return those values found in D12 to D14. When dates are specified in B1 & B2, it looks to B12, 13, 14 and C12,13, 14) and returns the appropriate cost value.
However whenever the date in B2, go beyond the dates it looks to, no value is returned. I would like to see it return the proper value in the appropriate cell. Can this be done? if so I would also like to expand it to look for a multiple of dates that I have shown in my chart B12 to B23 and C12 to C23. For clarity I have attached an example file.
Values are inputted in the following cells;
B1: Start Date
B2: End Date
B6: =IF($B$3=0,0,IF(AND($B$1>=B12,$B$2<=C12),D12,""))
B7: =IF($B$3=0,0,IF(AND($B$1>=B13,$B$2<=C13),D13,""))
B8: =IF($B$3=0,0,IF(AND($B$1>=B14,$B$2<=C14),D14,""))
I appreciate the help
Bookmarks