替代vlookup与精确和近似匹配不起作用

替代vlookup与精确和近似匹配不起作用

本文介绍了替代vlookup与精确和近似匹配不起作用的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

单元格A1:0553400710
单元格A2:John
单元格B1:['0553400710','0553439406']

注意:


  1. 列表项单元格B1有一个固定的格式为 ['number','number,'number',......]

  2. A1和A2是用户输入值

我想将单元格A1中的0553400710与单元格B1中的['0553400710','0553439406']匹配。 >

如果匹配,我想返回A2:John。



可以吗?



Vlookup无法正常工作。我正在寻找一些使用固定格式的优势的技巧



图片1:这是我尝试过的公式





图2:这是vlookup显示错误值的表





图3:这是vlookup检查的数组



解决方案

(和参考)在你的叙述和忽略的图像,一个简单的通配符匹配应该是足够的。

  = IFERROR(INDEX(A:A,MATCH(*& A1&*,B:B,0)+1),)

Cell A1: 0553400710
Cell A2: John
Cell B1: ['0553400710', '0553439406']

Note:

  1. List item Cell B1 has a fixed format of ['number','number,'number',...... ]
  2. A1 and A2 are user input values

I want to match 0553400710 in Cell A1 with ['0553400710', '0553439406'] in Cell B1.

If it matches, I want to return A2: John.

Is it possible?

Vlookup failed to work by the way. I am looking for some technique which uses the advantage of fixed format

Picture 1: This is the formula i have tried

Picture 2: This is the table where the vlookup is showing wrong values

Picture 3: This is the array where vlookup check

解决方案

Going by the sample data (and references) in your narrative and ignoring the image(s), a simple wildcard match should be sufficient.

=IFERROR(INDEX(A:A, MATCH("*"&A1&"*",B:B, 0)+1), "")

这篇关于替代vlookup与精确和近似匹配不起作用的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-21 05:11