Nested Xlookup not returning all data

[Mat1968]

Hi All,
First time poster & long time stalker here. I have a workbook that is used as part of work planning & have used a nested XLOOKUP to return data from sheets. I am not able to use ranges or named tables at this time due to end user constraints.
The function should - but does not - return data from the March to July inclusive sheets that is entered on the 1st day of each month. It does however return data from all other sheets.
AS an example on 'Planning Tab' enter the date 1/7/04 to cell 'B137' crew roster availability should populate to the range B116 through to B131. It does not unless - as per example - I delete the JUNE part of the function from cell B116.
I have checked formatting in all sheets as well as the referenced arrays & spelling, all seems OK. Is someone able to suggest something I may have missed please?
I am unable to upload a file at this time due to security settings, my apologies. I may be able to get permissions changed during the coming week.
Thanking you in advance,
Mat

The logic is & is repeated:
XLOOKUP(LOOK AT AN ENTERED DATE IN CELL B17,GOTO July!$C$1:$AJ$1 SHEET RANGE AND FIND THE DATE SPECIFIED,XLOOKUP(INVESTIGATE FORMATTING IN CELL A117 TO FIND THE CORRECT DATA RIGHT($A117,LEN($A117)-SEARCH(".",$A117)),GOTO July!$B$2:$B$100SHEET RANGE AND FIND THE DATA,GOTO July!$C$2:$AJ$100 AND RETURN THE REQQUIRED INFORMATION),REPEAT AS REQUIRED

The full nested XLookup is below with the JUNE part of the function I delete in testing italicized in bold. (ignore the spelling of February in this instance as it is consistent)
=IFERROR(XLOOKUP($B$137,January!$C$1:$AJ$1,XLOOKUP(RIGHT($A117,LEN($A117)-SEARCH(".",$A117)),January!$B$2:$B$101,January!$C$2:$AJ$101),
XLOOKUP($B$137,Febuary!$C$1:$AJ$1,XLOOKUP(RIGHT($A117,LEN($A117)-SEARCH(".",$A117)),Febuary!$B$2:$B$100,Febuary!$C$2:$AJ$100),
XLOOKUP($B$137,March!$C$1:$AJ$1,XLOOKUP(RIGHT($A117,LEN($A117)-SEARCH(".",$A117)),March!$B$2:$B$100,March!$C$2:$AJ$100),
XLOOKUP($B$137,April!$C$1:$AJ$1,XLOOKUP(RIGHT($A117,LEN($A117)-SEARCH(".",$A117)),April!$B$2:$B$100,April!$C$2:$AJ$100),
XLOOKUP($B$137,May!$C$1:$AJ$1,XLOOKUP(RIGHT($A117,LEN($A117)-SEARCH(".",$A117)),May!$B$2:$B$100,May!$C$2:$AJ$100),
XLOOKUP($B$137,June!$C$1:$AI$1,XLOOKUP(RIGHT($A117,LEN($A117)-SEARCH(".",$A117)),June!$B$2:$B$100,June!$C$2:$AI$100),
XLOOKUP($B$137,July!$C$1:$AJ$1,XLOOKUP(RIGHT($A117,LEN($A117)-SEARCH(".",$A117)),July!$B$2:$B$100,July!$C$2:$AJ$100),
XLOOKUP($B$137,August!$C$1:$AJ$1,XLOOKUP(RIGHT($A117,LEN($A117)-SEARCH(".",$A117)),August!$B$2:$B$100,August!$C$2:$AJ$100),
XLOOKUP($B$137,September!$C$1:$AJ$1,XLOOKUP(RIGHT($A117,LEN($A117)-SEARCH(".",$A117)),September!$B$2:$B$100,September!$C$2:$AJ$100),
XLOOKUP($B$137,October!$C$1:$AJ$1,XLOOKUP(RIGHT($A117,LEN($A117)-SEARCH(".",$A117)),October!$B$2:$B$100,October!$C$2:$AJ$100),
XLOOKUP($B$137,November!$AH$1:$AJ$1,XLOOKUP(RIGHT($A117,LEN($A117)-SEARCH(".",$A117)),November!$B$3:$B$100,November!$C$2:$AJ$100),
XLOOKUP($B$137,December!$C$1:$AJ$1,XLOOKUP(RIGHT($A117,LEN($A117)-SEARCH(".",$A117)),December!$B$2:$B$100,December!$C$2:$AJ$100))))))))))))),"")

 

[Mat1968]

Hi All,
First time poster & long time stalker here. I have a workbook that is used as part of work planning & have used a nested XLOOKUP to return data from sheets. I am not able to use ranges or named tables at this time due to end user constraints.
The function should - but does not - return data from the March to July inclusive sheets that is entered on the 1st day of each month. It does however return data from all other sheets.
AS an example on 'Planning Tab' enter the date 1/7/04 to cell 'B137' crew roster availability should populate to the range B116 through to B131. It does not unless - as per example - I delete the JUNE part of the function from cell B116.
I have checked formatting in all sheets as well as the referenced arrays & spelling, all seems OK. Is someone able to suggest something I may have missed please?
I am unable to upload a file at this time due to security settings, my apologies. I may be able to get permissions changed during the coming week.
Thanking you in advance,
Mat

The logic is & is repeated:
XLOOKUP(LOOK AT AN ENTERED DATE IN CELL B17,GOTO July!$C$1:$AJ$1 SHEET RANGE AND FIND THE DATE SPECIFIED,XLOOKUP(INVESTIGATE FORMATTING IN CELL A117 TO FIND THE CORRECT DATA RIGHT($A117,LEN($A117)-SEARCH(".",$A117)),GOTO July!$B$2:$B$100SHEET RANGE AND FIND THE DATA,GOTO July!$C$2:$AJ$100 AND RETURN THE REQQUIRED INFORMATION),REPEAT AS REQUIRED

The full nested XLookup is below with the JUNE part of the function I delete in testing italicized in bold. (ignore the spelling of February in this instance as it is consistent)
=IFERROR(XLOOKUP($B$137,January!$C$1:$AJ$1,XLOOKUP(RIGHT($A117,LEN($A117)-SEARCH(".",$A117)),January!$B$2:$B$101,January!$C$2:$AJ$101),
XLOOKUP($B$137,Febuary!$C$1:$AJ$1,XLOOKUP(RIGHT($A117,LEN($A117)-SEARCH(".",$A117)),Febuary!$B$2:$B$100,Febuary!$C$2:$AJ$100),
XLOOKUP($B$137,March!$C$1:$AJ$1,XLOOKUP(RIGHT($A117,LEN($A117)-SEARCH(".",$A117)),March!$B$2:$B$100,March!$C$2:$AJ$100),
XLOOKUP($B$137,April!$C$1:$AJ$1,XLOOKUP(RIGHT($A117,LEN($A117)-SEARCH(".",$A117)),April!$B$2:$B$100,April!$C$2:$AJ$100),
XLOOKUP($B$137,May!$C$1:$AJ$1,XLOOKUP(RIGHT($A117,LEN($A117)-SEARCH(".",$A117)),May!$B$2:$B$100,May!$C$2:$AJ$100),
XLOOKUP($B$137,June!$C$1:$AI$1,XLOOKUP(RIGHT($A117,LEN($A117)-SEARCH(".",$A117)),June!$B$2:$B$100,June!$C$2:$AI$100),
XLOOKUP($B$137,July!$C$1:$AJ$1,XLOOKUP(RIGHT($A117,LEN($A117)-SEARCH(".",$A117)),July!$B$2:$B$100,July!$C$2:$AJ$100),
XLOOKUP($B$137,August!$C$1:$AJ$1,XLOOKUP(RIGHT($A117,LEN($A117)-SEARCH(".",$A117)),August!$B$2:$B$100,August!$C$2:$AJ$100),
XLOOKUP($B$137,September!$C$1:$AJ$1,XLOOKUP(RIGHT($A117,LEN($A117)-SEARCH(".",$A117)),September!$B$2:$B$100,September!$C$2:$AJ$100),
XLOOKUP($B$137,October!$C$1:$AJ$1,XLOOKUP(RIGHT($A117,LEN($A117)-SEARCH(".",$A117)),October!$B$2:$B$100,October!$C$2:$AJ$100),
XLOOKUP($B$137,November!$AH$1:$AJ$1,XLOOKUP(RIGHT($A117,LEN($A117)-SEARCH(".",$A117)),November!$B$3:$B$100,November!$C$2:$AJ$100),
XLOOKUP($B$137,December!$C$1:$AJ$1,XLOOKUP(RIGHT($A117,LEN($A117)-SEARCH(".",$A117)),December!$B$2:$B$100,December!$C$2:$AJ$100))))))))))))),"")

Typo under 'The logic is & is repeated' - 'B17 should have been B137

 

[Mat1968]

I solved my issue by making sheet references a dynamic, thereby eliminating multiple nested functions. The original nested function has been reduced to the double look up sample below. It also reduces processing time.
the ($B$137)+1 is hard coded to a cell containing a date. Increasing the 1 to 2, 3 etc moves the date forward (handy for a weekly schedule). Using the same logic in an INDIRECT function & extracting the month allows a sheet tab to be referenced (January, February etc). The RIGHT($A132,LEN($A132)-SEARCH(".",$A132) component extracts the vertical lookup component from a text string, it too is dynamic.
Wrapping the entire function in an IFERROR, keeps everything neat if no data is returned.
regards,
Mat
=IFERROR(XLOOKUP(($B$137)+1,INDIRECT(TEXT(($B$137+1),"mmmm")&"!$C$1:$AJ$1"),XLOOKUP(RIGHT($A132,LEN($A132)-SEARCH(".",$A132)),INDIRECT(TEXT(($B$137+1),"mmmm")&"!$B$2:$B$100"),INDIRECT(TEXT(($B$137+1),"mmmm")&"!$C$2:$AJ$100"))),"")