Google表格中支持数组输入输出的类LOOKUP函数需求问询

Hey there! Great question—Google Sheets absolutely has ways to create an array-aware version of LOOKUP that does exactly what you need, and we can tailor it directly to your dependency status workflow. Let's break this down step by step.

First: Array-Compatible Alternatives to LOOKUP #

If you want a direct replacement for your proposed =LOOKUP(input_array, range_with_ID_values, range_with_return_values) formula (that returns an array of results), these are your best options:

1. XLOOKUP (Recommended, Native Array Support) #

XLOOKUP is the modern successor to LOOKUP/VLOOKUP and natively handles array inputs without extra wrapping. Use it like this:

=XLOOKUP(input_array, range_with_ID_values, range_with_return_values)

It will return an array of matching values corresponding to every item in input_array, with exact matching enabled by default (no need to tweak parameters for your use case).

2. ARRAYFORMULA + VLOOKUP #

If you prefer sticking with VLOOKUP (or need compatibility with older sheets), wrap it in ARRAYFORMULA to enable array processing:

=ARRAYFORMULA(VLOOKUP(input_array, {range_with_ID_values, range_with_return_values}, 2, FALSE))

The {range_with_ID_values, range_with_return_values} syntax combines your ID and return ranges into a single table, and FALSE ensures exact matches.

3. ARRAYFORMULA + INDEX/MATCH #

Another robust array-friendly option using classic functions:

=ARRAYFORMULA(INDEX(range_with_return_values, MATCH(input_array, range_with_ID_values, 0)))

MATCH returns the position of each input ID in your ID range, and INDEX pulls the corresponding value from the return range—ARRAYFORMULA makes this work for the entire input array.

Applying This to Your Dependency Status Workflow #

Now let's tailor this to your specific goal: automatically update a task's status to Pending only if all its dependencies are marked Completed (otherwise keep it as Awaiting Dependency or its current state).

Assumptions About Your Sheet Structure #

Let's assume your sheet has:

• Column A: Task IDs (unique identifiers for each task)
• Column B: Current task status (e.g., "Awaiting Dependency", "Pending", "Completed")
• Column C: Dependent task IDs (either comma-separated in a single cell like "Task1, Task3", or a range of cells with individual IDs)

Formula for Comma-Separated Dependencies #

If your dependencies are in a single cell (e.g., C2 has "Task1, Task3"), use this formula in the status column (or a helper column):

=IF(
  IFERROR(AND(XLOOKUP(SPLIT(C2, ", "), A:A, B:B)="Completed"), FALSE),
  IF(B2="Awaiting Dependency", "Pending", B2),
  B2
)

Breaking this down:

1. SPLIT(C2, ", ") turns your comma-separated dependency IDs into an array.
2. XLOOKUP(...) fetches the status of each dependency.
3. AND(...) checks if all dependency statuses are "Completed" (we use IFERROR to handle cases where a dependency ID doesn't exist, defaulting to FALSE).
4. The outer IF updates the status to "Pending" only if all dependencies are done and the current status is "Awaiting Dependency"—otherwise, it keeps the original status.

Formula for Range-Based Dependencies #

If your dependencies are in a range (e.g., C2:E2 lists individual dependent IDs), simplify the formula by using the range directly as the input array:

=IF(
  IFERROR(AND(XLOOKUP(C2:E2, A:A, B:B)="Completed"), FALSE),
  IF(B2="Awaiting Dependency", "Pending", B2),
  B2
)

Key Notes #

• Error Handling: The IFERROR ensures your sheet doesn't break if a dependency ID is mistyped or deleted.
• Exact Matches: All these formulas use exact matching (critical for task IDs—you don't want accidental partial matches).
• Drag to Apply: If you're using a helper column, drag the formula down to apply it to all tasks.

内容的提问来源于stack exchange，提问作者K Shukla