在下面数据中找出与0.46最相近的数值,并返回
公式为:
=IF(ABS(MINIFS(A2:A13,A2:A13,">"&B2)-B2)<ABS(MAXIFS(A2:A13,A2:A13,"<"&B2)-B2),MINIFS(A2:A13,A2:A13,">"&B2),MAXIFS(A2:A13,A2:A13,"<"&B2))
如图所示:
搞错了,其实也没那么烦
公式越简单越好
C2=LOOKUP(1,0/(ABS(A:A-B2)=SMALL(ABS(A:A-B2),1)),A:A),
ctrl+shift+回车