EXCEL公式内大括号里面的数字太长了怎么办？LOOKUP(Sheet1!$B3,Sheet2!$A$8:$A$97,{8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,

Question

EXCEL公式内大括号里面的数字太长了怎么办？
LOOKUP(Sheet1!$B3,Sheet2!$A$8:$A$97,{8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97})

Answer 1

公式精简为这样：
=LOOKUP(Sheet1!$B3,Sheet2!$A$8:$A$97,ROW($8:$97))
或加入防空白判断，当Sheet1!$B3为空白时，公式也返回空白，以上公式返回的是#N/A错误值。
=IF(Sheet1!$B3,="","",LOOKUP(Sheet1!$B3,Sheet2!$A$8:$A$97,ROW($8:$97)))
公式的意思为：以Sheet1!$B3的值在Sheet2!$A$8:$A$97中查找最接近Sheet1!$B3的值且比它小的数值，找到后返回对应的8-97这个范围中的数字。
公式中的ROW($8:$97)要与单元格的实际总行数对应，如$A$8:$A$97共90行，那么ROW($8:$97)也是产生90个数字，这部份其实写作ROW($1:$90) EXCEL 也是不会提示出错的，只是结果返回的是1-90之间的数值。