Maybe LOOKUP isn't the best function?

mikeross

New Member
Joined
Aug 13, 2013
Messages
19
MZ8IrIf.png



Basically, you can see clients on the left and then different stages of the project next to it. These dates are manually entered. The "(IR)" is basically our internal review and has to get priority if it's in the same week as the same stage without "(IR)"

Because it has to get priority I did a basic nested if that goes one by one and sees if the week is an IR week and if it is, show IR

After that, if a week is not an IR week, I did a LOOKUP to find what phase that date would fall under. If it was an IR date, that means we already have that IR listed, so I just put the stage without IR.

It's pretty much working but it's very unnecessarily completed I feel...

Here is the formula from cell L3:
Code:
=IFERROR(IF(AND($C3>=L$1,$C3<=L$2),$C$2,IF(AND($E3>=L$1,$E3<=L$2),$E$2,IF(AND($G3>=L$1,$G3<=L$2),$G$2,IF(LOOKUP(L$1,$B3:$K3,$B$2:$K$2)=$C$2,$D$2,IF(LOOKUP(L$1,$B3:$K3,$B$2:$K$2)=$E$2,$F$2,IF(LOOKUP(L$1,$B3:$K3,$B$2:$K$2)=$G$2,$H$2,LOOKUP(L$1,$B3:$K3,$B$2:$K$2))))))),"")

(Note: there is a formula in column K that just adds 7 to the END date so that it doesn't keep repeating END END END after a project is done)

Problem:

Look at cell AA5. This cell should "5" as phase 5 starts on 9/1 and that is in the week. However, the LOOKUP function moves over one and puts it into the next week. How can I fix this?

Is there just a way easier way to do this overall?

Thank you for reading, helping
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Forum statistics

Threads
1,213,557
Messages
6,114,291
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top