问题:返回最接近目标值的数字

问题:返回最接近目标值的数字

=MIN(IF(ABS($A$2:$A$23-C2)=MIN(ABS($A$2:$A$23-C2)),$A$2:$A$23,""))

数组3键

这个公式,相同最接近的话,就返回小的值

要大的值就用

=MAX(IF(ABS($A$2:$A$23-C2)=MIN(ABS($A$2:$A$23-C2)),$A$2:$A$23,""))

都要就用

=TEXTJOIN(",",1,IF(ABS($A$2:$A$23-C2)=MIN(ABS($A$2:$A$23-C2)),$A$2:$A$23,""))

UP:小赖同学爱学习