sorting - 数组公式在一行中找到关闭值

Question

我试图将其转换为在数组中找到最接近的匹配项，但是，我认为您不能那样做。

=VLOOKUP(a1,SORT(range,1,TRUE),2)

=ArrayFormula(iferror(VLOOKUP(Q2:Q,SORT(R2:S,1,TRUE),2),""))

关于如何制作一个连续找到关闭值的数组公式的任何其他想法？

标签： sortingif-statementgoogle-sheetsgoogle-sheets-formulaarray-formulas

Answer 1

尝试：

=ARRAYFORMULA(IF(ABS(B2:B-C2:C)<ABS(B2:B-D2:D), C2:C, D2:D))

---

对于多列：

=ARRAYFORMULA(TRIM(TRANSPOSE(QUERY(TRANSPOSE(
 IF(QUERY(TRANSPOSE(QUERY(TRANSPOSE(ABS(B2:B-C2:G)), 
 "select "&TEXTJOIN(",", 1, "min(Col"&ROW(B2:B)-ROW(B2)+1&")")&"")), 
 "select Col2")=ABS(B2:B-C2:G), C2:G, )),,99^99))))