Nested formula with IFBLANK and XLOOKUP

justenough

New Member
Joined
Aug 18, 2024
Messages
2
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I have a task list table with start dates in E and end dates in F
Task names are in C
User also has the option of selecting a task name in G (taken from the tasks listed in C) if the task in C can be performed at the same time as the task listed in G
Dates are dependent on the dates in the row above, UNLESS there is a dependent task name in G
If there is a task name in G then I need to check this value against the values in C, then populate E with what's in the matching C row's E cell

So:
C: task name
D: number of days of task (irrelevant here)
E: start date for task
F: end date for task
G: optional parallel task name

Using row 6 as an example (populating E6 with start date), these are the conditions:
- check if F5 is blank, if it is then make E6 cell blank as well (do not proceed with anything else, leave E6 blank)
- if F5 is not blank then check if G6 is blank
- if G6 is blank then populate E6 with F5+1 work day (i.e. make E6 = one day after F5)
- if G6 is not blank then find the matching G6 value in the C column and then populate E6 with the same value as column E for the row that the matching value is found in C

My first couple tries I ended up with this for E6 which I know is wrong:
IF((AND(ISBLANK(F5),""),
(IF(ISBLANK(G6),(WORKDAY(F5,1,Holidays)),
(XLOOKUP(G6,C6:C50,E6:E50,"ERROR",0)))))

Pretty sure there are fundamental issues with it unrelated to the number of parentheses but I'm stumped. Any help is greatly appreciated.; not sure if the functions used are the best for this.
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Sorry, I'm not sure how to edit my original post but need to update the conditions. Please use THIS instead of the original for query info :)

I have a task list table with start dates in E and end dates in F
Task names are in C
User also has the option of selecting a task name in G (taken from the tasks listed in C) if the task in C can be started at the same time as the task listed in G
Dates are dependent on the dates in the row above, UNLESS there is a dependent task name in G
If there is a task name in G then I need to check this value against the values in C, then populate E with what's in the matching C row's E cell

So:
C: task name
D: number of days of task (irrelevant here)
E: start date for task
F: end date for task
G: optional parallel task name

Using row 6 as an example (populating E6 with start date), these are the conditions:
- check if C6 is blank, if it is then make E6 cell blank as well (do not proceed with anything else, leave E6 blank)
- if C6 is not blank then check if G6 is blank
- if G6 is blank then populate E6 with F5+1 work day (i.e. make E6 = one day after F5)
- if G6 is not blank then find the matching G6 value in the C column and then populate E6 with the same value as column E for the row that the matching value is found in C

My first couple tries I ended up with this for E6 which I know is wrong:
IF((AND(ISBLANK(C6),""),
(IF(ISBLANK(G6),(WORKDAY(F5,1,Holidays)),
(XLOOKUP(G6,C6:C50,E6:E50,"ERROR",0)))))

Pretty sure there are fundamental issues with it unrelated to the number of parentheses but I'm stumped. Any help is greatly appreciated.; not sure if the functions used are the best for this.
 

Attachments

  • Capture.JPG
    Capture.JPG
    33.3 KB · Views: 19
Upvote 0

Forum statistics

Threads
1,224,257
Messages
6,177,473
Members
452,781
Latest member
Latlonchy

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