Xlookup函数,我们曾见识过它把Vlookup打进冷宫的样子。可有一个函数不服,它就是查找一哥 Lookup函数,今天兰色带大家看一场查找界的双龙会,看一下到底是Lookup牛,还是Xlookup更厉害。 1、普通查找 Xlookup =XLOOKUP(A11,A2:A7,C2:C7) Lookup =LOOKUP(1,0/(A2:A7=A11),C2:C7) 简评:xlookup比lookup更简捷 2、反向查找 Xlookup =XLOOKUP(A11,B2:B7,A2:A7) Lookup =LOOKUP(1,0/(B2:B7=A11),A2:A7) 简评:同查找1,xlookup更简捷 Xlookup =XLOOKUP(A11&B11,A2:A7&B2:B7,D2:D7) Lookup =LOOKUP(1,0/((A2:A7=A11)*(B2:B7=B11)),D2:D7) Xlookup =XLOOKUP(A11,A2:A7,B2:B7,,-1) Lookup =LOOKUP(A11,A2:B7) Xlookup=XLOOKUP(A11,B2:B7,C2:C7,,,-1) Lookup =LOOKUP(1,0/(B2:B7=A11),C2:C7) Xlookup =XLOOKUP(A11,A2:A7,C2:C7,'') Lookup =IFERROR(LOOKUP(1,0/(A2:A7=A11),C2:C7),'') Xlookup=XLOOKUP(9^9,--RIGHT(A3,ROW(1:10)),--RIGHT(A3,ROW(1:10)),,-1) Lookup =LOOKUP(9^9,--RIGHT(A3,ROW(1:10))) Xlookup =XLOOKUP(9^9,FIND(A7,A$2:A4),B$2:B4,,-1) Lookup =LOOKUP(9^9,FIND(A7,A$2:A4),B$2:B4) |
|