如何让Excel的LOOKUP函数处理双数组条件，筛选不大于今日的最后日期？

Hey there! Let's break down why your modified formula stopped working and get it back on track.

The Root of the Problem #

Your original formula (something like =LOOKUP(2,1/(A2:Z2<>""),A1:Z1)) works because it creates an array where non-empty cells become 1 (from 1/(A2:Z2<>"")) and empty cells become errors. LOOKUP ignores errors and finds the last 1, mapping it to the header row.

When you added AND(A2:Z2<>"", A2:Z2<=TODAY()), here's what went wrong: Excel's AND function returns a single TRUE/FALSE value for the entire range, not an array of results for each cell. That means 1/AND(...) becomes either 1 (if all cells meet both conditions) or a single error (if any don't)—so LOOKUP can't pinpoint the specific cell you need.

The Fix: Replace AND with Multiplication #

In Excel array logic, using * acts like a logical "AND" for arrays. It converts TRUE/FALSE values to 1/0, so only cells that meet both conditions will result in 1 (1*1=1), while any mismatch gives 0.

Here's your updated formula:

=LOOKUP(2,1/((A2:Z2<>"")*(A2:Z2<=TODAY())),A1:Z1)

How This Works Step-by-Step #

1. (A2:Z2<>""): Checks each cell for non-empty values, returns an array of TRUE/FALSE.
2. (A2:Z2<=TODAY()): Checks each date to see if it's today or earlier, returns another TRUE/FALSE array.
3. Multiplying the two arrays: Only cells where both are TRUE become 1; all others become 0.
4. 1/: Converts 1 to 1 (valid) and 0 to #DIV/0! (error, which LOOKUP ignores).
5. LOOKUP(2, ...): Scans from left to right, ignores errors, and grabs the last valid 1's corresponding header value.

Bonus: XLOOKUP Alternative (Excel 365/2021+) #

If you're on a newer Excel version, XLOOKUP makes this logic more readable. It lets you explicitly search from the end of the range:

=XLOOKUP(TRUE,(A2:Z2<>"")*(A2:Z2<=TODAY()),A1:Z1,,0,-1)

The -1 at the end tells XLOOKUP to start searching from the last cell and move left until it finds the first match.

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