using XLOOKUP to find matching names over multiple tabs and supplying data from specific cells as a result

I have 4 tabs in my worksheet:

TAB 1 (Main TAB): OPS SCHEDULE 2025
TAB 2: PIER1 - (This has a get data from the web to bring in the table)
TAB 3: PIER2 - (This has a get data from the web to bring in the table)
TAB 4: POINT - (This has a get data from the web to bring in the table)

I have a function in column A that searches TAB 2, 3 and 4 for a matching vessel name, if vessel name matches it will bring in the date from Column D(STACKS OPEN). This formula works perfectly for Tab 2 and Tab 4 when putting any of the vessel names in but for TAB 2 it for some reason only searches to a certain point and then does not find the lower half of the vessels and gives me a "" outcome.

OPS SCHEDULE 2025:

PIER 1:

PIER 2:

POINT:

function used in STACKS OPEN column:

=IFERROR(
    DATEVALUE(
        LEFT(
            IFERROR(
                XLOOKUP(C5, PIER1!A:A, PIER1!D:D),
                IFERROR(
                    XLOOKUP(C5, PIER2!A:A, PIER2!D:D),
                    XLOOKUP(C5, POINT!A:A, POINT!D:D, "")
                )
            ),
            LEN(
                IFERROR(
                    XLOOKUP(C5, PIER1!A:A, PIER1!D:D),
                    IFERROR(
                        XLOOKUP(C5, PIER2!A:A, PIER2!D:D),
                        XLOOKUP(C5, POINT!A:A, POINT!D:D, "")
                    )
                )
            )
        )
    ),
    ""
)

I am not sure if there is some way to upload the file but any help will be hugly appreciated as this is above my level and I cant wrap my head around why it just stops giving results to a certain point on PIER2.

Solution

If you click Transform Data before loading your data you will be able to make significant improvements to the readability of your data.

I've made a mock up of your Pier1 data table and clicked the Transform Data button.

On the Home toolbar you can see the Remove Rows button. Click that and select Remove Top Rows.
Remove the top 3 rows in the dialog box that opens.

Again on the Home toolbar you'll find the Use First Row as Headers button. Click that so your columns are named VESSEL NAME, O/B VYG NO. and so on.

Your table should now look like the image below, with the STACKS OPEN and STACK CLOSE columns recognised as Date/Time data types.
You can click the Down-Arrow on any of the first three columns and filter out those null values if you want.

Click the date/time icon and change the data type to just a date. Select Add a Step rather than replacing the existing step here if it asks.

Double-click the column headers and give each column a name that you will use across all three queries - when appending the tables to each other they will need the same names.

Click the drop-down on the Close & Load button and opt to Only Create Connection as you want to join all three tables up before loading onto a sheet.

Do the same thing for Pier 2 - it's pretty much the same.

Things are slightly different for Point - the date and time are in different columns.

Remove the top row using the Remove Rows button.

Click the header of Column5, hold Ctrl and click the header of Column7 to select both columns and then click Remove Columns (just to the left of Remove Rows).

User First Row as Headers again. Your date columns should get recognised as dates again.

Rename your columns.

Select Append Queries from the home tab (can either append in the current query or create a new one). Select Three or more tables and select the tables to append.

Finally select Close and Load and select to a Table either in the Existing Sheet or a New worksheet.

Once it's loaded you can use the formula =XLOOKUP(C5,Point_Source_1[Vessel],Point_Source_1[STACKS OPEN],"") to find your value.

Note: You can change the name of the table here -

Edit: As you can see I forgot to rename one of the columns so I've got both a Vessel and a VESSEL NAME column.