Excel公式需求：查找最新1之前的最后一个非1值

Got it, let's work through this problem step by step. The issue with your initial formula MATCH(MIN(range),range,0)+1 is that it only targets the first occurrence of the minimum value (1), but we need to focus on the last occurrence of 1 instead, then locate the nearest non-1 value right before it.

Solutions tailored to your Excel version: #

1. For Excel 365/2021 (Simplest: XLOOKUP) #

If you have access to XLOOKUP, this formula is clean and intuitive. Assume your data lives in range A1:A10:

=XLOOKUP(FALSE, A1:INDEX(A:A, MATCH(1, A:A, 1))=1, A1:INDEX(A:A, MATCH(1, A:A, 1)),, 0, -1)

How it works: #

• MATCH(1, A:A, 1): Finds the row number of the last occurrence of 1. The 1 as the third argument tells MATCH to search for the largest value ≤ 1, which lands on the final 1 (since 1 is your minimum value).
• A1:INDEX(A:A, MATCH(...)): Truncates your data range to include everything from the start up to that final 1.
• XLOOKUP(FALSE, [truncated range]=1, [truncated range],, 0, -1): Searches backwards (via the -1 argument) through the truncated range to find the first cell that isn't 1—this is exactly the last non-1 value before the final 1.

2. For Older Excel Versions (Array Formula) #

If you're using an older Excel version without XLOOKUP support, use this array formula (remember to press Ctrl+Shift+Enter after typing it, not just Enter):

=INDEX(A:A, MAX(IF(A1:INDEX(A:A, MATCH(1, A:A, 1))<>1, ROW(A1:INDEX(A:A, MATCH(1, A:A, 1))))))

How it works: #

• Same MATCH(1, A:A, 1) to get the final 1's position.
• IF(A1:INDEX(...)<>1, ROW(...)): Creates an array where non-1 cells are replaced with their row numbers, and 1 cells return FALSE.
• MAX(...): Grabs the largest row number from that array (this corresponds to the last non-1 cell before the final 1).
• INDEX(A:A, ...): Pulls the value from that row.

3. Alternative with LOOKUP (No Array Entry Required) #

Another option that works in most Excel versions without needing array entry:

=LOOKUP(2, 1/(A1:INDEX(A:A, MATCH(1, A:A, 1))<>1), A1:INDEX(A:A, MATCH(1, A:A, 1)))

How it works: #

• 1/(A1:INDEX(...)<>1): Generates an array where non-1 cells become 1 and 1 cells become #DIV/0! errors (which LOOKUP automatically ignores).
• LOOKUP(2, 1/..., [range]): LOOKUP searches for the largest value ≤ 2 (which is the last 1 in the error-free array) and returns the corresponding value from your data range.

Example Adjustment for Your Specific Scenario #

You mentioned your example should return 3 instead of 4—this suggests you might be looking for the last non-1 value before the first occurrence of 1 (not the last one). In that case, swap MATCH(1, A:A, 1) with MATCH(1, A:A, 0) (which finds the first 1) and adjust the range to stop just before that first 1:

=XLOOKUP(FALSE, A1:INDEX(A:A, MATCH(1, A:A, 0)-1)=1, A1:INDEX(A:A, MATCH(1, A:A, 0)-1),, 0, -1)

For a sample range like [2, 3, 1, 4, 1], this formula will return 3 as expected.

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