应用场景:收到4家公司的办公设备投标报价,现需要对其中的关键项目进行价格核对,投标公司的报价表格格式如表一,核对表格格式如表二,需要根据核对表格第1、第2行的品牌型号及配件名称,从投标报价表格中查找出相应品牌型号的对应配件价格。
这种需求一般用Vlookup函数实现,但可以看到,投标报价表的品牌型号以合并单元格的形式呈现,而配件名称跟随品牌型号以单行形式呈现,怎样在这种形式的表格中获得单价呢?
表一(投标报价表):
表二(价格核对表):
步骤一:在投标报价表中建立辅助列
既然合并单元格只有最上面一格有值,需要把其他单元格进行对应的赋值。思路是空白单元格的值等于其上方最近一个非空单元格。辅助列设在G2,用到的公式是=LOOKUP(1,0/($B$1:B2<>""),$B$1:B2),注意此处的B2是不锁定的,双击G2单元格右下角十字,即可将公式延续到下方单元格,B2变为B3、B4……
如图:
步骤二:应用Vlookup组合查找,注意被查询表格的列号均需要用$锁定
vlookup公式:=VLOOKUP(B1&B2,IF({1,0},成功!$G:$G&成功!$C:$C,成功!$E:$E),2,0)
第一个参数为需要查找的值,这里是B1&B2,即两个单元格文本的组合(富士通1000K打印头)
第二个参数为需要查找的范围,用if({1,0})方式,将第G/C列的组合文本列(品牌型号&配件名称)与第E列(单价)组合起来形成查询范围
第三个参数为需要返回的值(在查找范围的第几列),查找范围第一列为组合文本(品牌型号&配件名称),第二列为单价,应返回第二列,参数为2
第四个参数为是否精确查找。这里需要精确匹配,参数为0。
如图:
横向拉拽单元格,即可生成该公司所有关键配件的价格:
修改公式里的工作表名称,即可匹配对应到不同公司的报价表: