In column A, I have a list of sheet names in YYMM format

YYMM
2506
2507
2508

In column B, I want a lookup against those sheets to find the string Total days and take the value. The full formula is

= iferror(
  byrow(
    A2:A,
    LAMBDA(
      YYMM,
      VLOOKUP("Total days", INDIRECT(YYMM&"!A:D"), 4, 0)
    )
  ),
""
)

• The vlookup is taking the value from column D, where Total days is found on sheets 2506, 2507, and 2508.

• The lambda is creating the variable YYMM to feed into each indirect vlookup

• The byrow function is iterating over A2:A

• The iferror just makes it blank if there is no existing sheet

Inspired by Saturnine comment but a slight variant