[已解决]求教：vlookup与row和COLUMN的配合查找

yy_hyd

最好是传个例子给我，答好了给好评

最佳答案

月排行榜 / 总排行榜

老糊涂

2011-2-13 12:25

实例1：返回多个符合条件的查询结果

b140	姓名	工号
1	张三丰	KT001
1	李四光	KT002
1	李四光	KT003
2	张三丰	KT004
3	张三丰	KT005

查找的结果不止一个，并要全部返回，vlookup对于包含多个结果的查找只会返回第一个结果（数据区由上向下第一个）但有时需要返回所有的结果。如例，需要按姓名查找同名的各个工号，这种情况一般使用数组公式解决。但数据量大时，使用数组公式会由于计算量大，速度会很慢，vlookupp没有直接的解决方法，需要添加辅助列（注意辅助列需添加在左侧，因为要根据辅助列的内容查找），表五左侧白色区域为辅助列，B141公式为=($C$148=C141)+B140；等同=IF($C$148=C141,1,0)+B140该公式结果为：如姓名列内容与查找姓名相同，则按顺序显示1、2、3……，从而将相同姓名区分，然后，在查询中查找数值1、2、3……即可查找到对应查找内容的多个结果(注意查找区域由辅助列开始）为便于复制，vlookup第一个参数数值1、2、3……使用函数row()返回的行号产生，当然，辅助列中的公式可以改为其它，只要达到区别相同内容的目的

姓名c147	工号
张三丰	KT001
	KT004
	KT005

=IF(ISNA(VLOOKUP(ROW(1:1),$B$141:$D$145,3,0)),”",VLOOKUP(ROW(1:1),$B$141:$D$145,3,0))

实例2：依次序返回同一查询结果的多列内容
【表一】

姓名b8	工号c8	性别d8	籍贯e8	出生年月f8
张三丰	KT001	男	北京	1970年8月
李四光	KT002	女	天津	1980年9月
王麻子	KT003	男	河北	1975年3月
赵六儿	KT004	女	河南	1985年12月

按姓名在上表中查询全部内容，当查询某个结果的多列内容，并要依次取出，可使用column()函数作为index_num参数，column()函数返回公式所在列的列号，当公式复制时，会随单元格的移动相应改变，lookup_value参数使用混合引用，如$B96。即对列标绝对引用（列标前加$）、行号相对引用(行号前没有$)；复制时引用单元格会改变。上面公式只要输入C96中的公式，然后向右、向下复制就可以。
=VLOOKUP($B99,$b$8:$f$12,COLUMN()-1,0)

姓名b98	工号c98	性别d98	籍贯e98	出生年月
李四光	KT002	女	天津	1980年9月
王麻子	KT003	男	河北	1975年3月

跳转到最佳答案楼层

老糊涂

实例1：返回多个符合条件的查询结果

b140	姓名	工号
1	张三丰	KT001
1	李四光	KT002
1	李四光	KT003
2	张三丰	KT004
3	张三丰	KT005

查找的结果不止一个，并要全部返回，vlookup对于包含多个结果的查找只会返回第一个结果（数据区由上向下第一个）但有时需要返回所有的结果。如例，需要按姓名查找同名的各个工号，这种情况一般使用数组公式解决。但数据量大时，使用数组公式会由于计算量大，速度会很慢，vlookupp没有直接的解决方法，需要添加辅助列（注意辅助列需添加在左侧，因为要根据辅助列的内容查找），表五左侧白色区域为辅助列，B141公式为=($C$148=C141)+B140；等同=IF($C$148=C141,1,0)+B140该公式结果为：如姓名列内容与查找姓名相同，则按顺序显示1、2、3……，从而将相同姓名区分，然后，在查询中查找数值1、2、3……即可查找到对应查找内容的多个结果(注意查找区域由辅助列开始）为便于复制，vlookup第一个参数数值1、2、3……使用函数row()返回的行号产生，当然，辅助列中的公式可以改为其它，只要达到区别相同内容的目的

姓名c147	工号
张三丰	KT001
	KT004
	KT005

=IF(ISNA(VLOOKUP(ROW(1:1),$B$141:$D$145,3,0)),”",VLOOKUP(ROW(1:1),$B$141:$D$145,3,0))

实例2：依次序返回同一查询结果的多列内容
【表一】

姓名b8	工号c8	性别d8	籍贯e8	出生年月f8
张三丰	KT001	男	北京	1970年8月
李四光	KT002	女	天津	1980年9月
王麻子	KT003	男	河北	1975年3月
赵六儿	KT004	女	河南	1985年12月

按姓名在上表中查询全部内容，当查询某个结果的多列内容，并要依次取出，可使用column()函数作为index_num参数，column()函数返回公式所在列的列号，当公式复制时，会随单元格的移动相应改变，lookup_value参数使用混合引用，如$B96。即对列标绝对引用（列标前加$）、行号相对引用(行号前没有$)；复制时引用单元格会改变。上面公式只要输入C96中的公式，然后向右、向下复制就可以。
=VLOOKUP($B99,$b$8:$f$12,COLUMN()-1,0)

姓名b98	工号c98	性别d98	籍贯e98	出生年月
李四光	KT002	女	天津	1980年9月
王麻子	KT003	男	河北	1975年3月

yy_hyd

你本身就是最佳{:011:}{:011:}

myaxlxh