替代vlookup与精确和近似匹配不起作用 [英] Alternative to vlookup with exact and approximate match doesnt work

问题描述

单元格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与精确和近似匹配不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！