【原】对账终极大招来了！最头疼的名称不一致，终于有救了

前2天的对账文章，粉丝认为可以直接用查找替换把名称改成一样，如果只是几个名称确实可以，实际表格有上千个名称，不太现实。另外，实际上的简称比文章的更乱。

VIP学员的问题，左边公司按简称记录，右边按全称记录，现在要3个条件互相核对。实际会出现内蒙牛羊、内蒙古牛羊，还有公司会存在空格等情况。

遇到这种情况，如果只是用VLOOKUP函数加通配符*，只能解决连续字符的简称查找，不连续字符的简称是无法查找到的。

=VLOOKUP("*"&A3&"*",G:H,1,0)

下面分享一条终极公式，能解决这种难题，按Ctrl+Shift+Enter三键结束。

=SUBSTITUTE(INDEX(G:G,RIGHT(MAX(MMULT(1-ISERR(SEARCH(MID(A3,COLUMN(A:Z),1),$G$3:$G$9)),ROW($1:$26)^0)/1%%+ROW($3:$9)),3))," ",)

这么长的公式要完全理解很难，不过没关系，只要会套用就行，卢子将需要修改的部分换成中文说明。

=SUBSTITUTE(INDEX(返回哪一列,RIGHT(MAX(MMULT(1-ISERR(SEARCH(MID(查找值,COLUMN(A:Z),1),在哪一列查找)),ROW($1:$26)^0)/1%%+ROW(全称的开始行到结束行)),3))," ",)

接下来的思路跟前2天一样，借助合并计算。将公司和订单号连接起来。

=SUBSTITUTE(INDEX(G:G,RIGHT(MAX(MMULT(1-ISERR(SEARCH(MID(A3,COLUMN(A:Z),1),$G$3:$G$9)),ROW($1:$26)^0)/1%%+ROW($3:$9)),3))," ",)&B3

同理，右边也用&连接起来，再嵌套SUBSTITUTE去除空格。

=SUBSTITUTE(G3," ",)&H3

插入列后，发现COLUMN(A:Z)变成COLUMN(A:AA)，从而导致出错。

需要重新改回来，也就是说在使用复杂的公式，尽量别插入行、列，容易导致公式出错。

=SUBSTITUTE(INDEX(G:G,RIGHT(MAX(MMULT(1-ISERR(SEARCH(MID(A3,COLUMN(A:Z),1),$G$3:$G$9)),ROW($1:$26)^0)/1%%+ROW($3:$9)),3))," ",)&B3

还有一个注意点，金额的标题两边改成不一样，要不然就汇总起来，没法判断差异。

选择L2，点数据，合并计算，分别引用两个区域，添加，勾选首行、最左边，确定。

这样就将两个表合并在一起。

最后添加一列差异，金额相减，0就是两边一样，不为0证明不同。

=M3-N3

还有一种情况，内容是从系统导出，金额为文本格式，需要转换成数值格式才行。选择区域，点感叹号，转换为数字。

对账其实不难，难的是处理数据源的过程，各种不规范的格式都可能发生，有时真的挺让人抓狂。

作者：卢子，清华畅销书作者，《Excel效率手册 早做完，不加班》系列丛书创始人，个人公众号：Excel不加班（ID：Excelbujiaban）