Vlookup函数完成查找与引用,表达=vlookup(查找值,查找区域,返回第几列,精确匹配或模糊匹配)
Match函数完成查找,表达=match(找谁,在哪找,精确匹配或模糊匹配),这个返回的是一个位置。
Index函数完成引用,表达=index(引用区域,具体位置),这个返回一个值。
Match+index函数就可以完成vlookup函数的工作,也能弥补vlookup函数的不足(查找值必须是查找区域的第一列)
例如,上一篇中的例子就可以用match+index函数共同完成
上一篇文章中,用vlookup完成的查找和返回公司名称,这次用index函数嵌套match函数完成
这是match函数的返回值,返回一个位置代号。
这是index函数引用的值。
=INDEX(数据源!B:B,MATCH(查询!A2,数据源!A:A,0))
Index函数表示从一个区域中引用一个值,所以函数的意思是从数据源表的B列去找一个值,具体位置让match函数来表达。
这里match函数的意思是找当前表中的A2单元格,去数据源表的A列找,精确匹配。返回一个位置,而客户ID与公司名称同行,所以它们在各列的位置一样,两个函数可以嵌套。
向前查找引用
因为vlookup函数的查找值必须在查找区域的第一列,所以它不能在不改变数据源表的情况下,向前查找引用,而index+match函数的配合可以完成。
例,通过客户名称查找客户id
=INDEX(数据源!A:A,MATCH(A2,数据源!B:B,0))
这个函数和上面的一样,就是把列换了一下,而vlookup就不能完成向前查询。
复习混合引用
这里详细讲了引用方式
一次输入完成乘法表
我们只需要在引用单元格时固定第一行,固定第一列,就完成了这个九九乘法表。
利用vlookup函数配合column函数一次返回多列数据
=Column()返回引用单元格的列数,
例,
返回C3单元格的列数,那就是3,如果参数不写就是返回当前单元格的列数。
这个表我们利用vlookup函数写都会,不过需要写好几遍,就是不断的改返回第几列。但是配合column函数就可以完成只写一个函数完成整个表的引用,同时注意混合引用。
=VLOOKUP($D4,数据源!$A:$K,COLUMN()-3,0)
首先解释,column()-3,这个在vlookup函数里代表返回查找区域的第几列,公司名称在查找区域也就是数据源表的第二列,所以就是column当前单元格的列数第五列减去3,联系人姓名在数据源表的第三列,column当前单元格列数第六列减去3,依次类推,我们通过column函数减去3完成了返回列的自动变化。
然后解释$D4,在向右拖动时查找值D4会变成E4,F4,G4,H4等因为相对引用代表的意思是相对它左边的单元格,所以我们需要D列不动,为了向下拖动方便我们要让行号变化,所以要用混合引用,把D4变成$D4.
查找区域,数据源表的A到K列要绝对引用,因为我们向右拖动了函数,如果使用相对引用,A到K会变成B到L,以此类推。为什么做一列数据的vlookup,选中列到列不用绝对引用呢?因为在一列数据填充的时候变得是行号,而列到列的选择包含了所有行。
这样我们只写一个公式,然后向右拖动,向下拖动就完成了整表的填列。