用 match 反方向 search 最後一個"同等值"

EXCEL DATE 


A: 4 5 4 3 6 7 5 4 5 0 2 6 3
B: 5




=MATCH(2,1/(A1:A13=B1))

confirmed with CTRL+SHIFT+ENTER

formula checks each cell against the target value with (A1:A13=B1) in the array formula, giving an array with TRUE (or 1) for the positions where the cells match, and with FALSE (or 0) for the rest. Dividing 1 by this results in 1s wherever that array was TRUE, and #DIV/0! wherever that array was false. So his formula is evaluated as

=MATCH(2,{#DIV/0!,1,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,1,#DIV/0!,1,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!})

Since no '2' is found in the array, and the last value (1) is found in the 9th position, the MATCH returns 9