在一个单元格范围内找到一个值作为子字符串 [英] Find a value in a range of cells as a substring

问题描述

在选项卡1上，我有一列，其中该列中的每个单元格都有一组数字，而另一列中该组数字与一个名称相关联. 示例:

On tab 1, I have a column where each cell in that column has a group of numbers, and another column where that group of numbers is associated with a name. Example:

A1: 131,210,312,419,5010 B1: Jim
A2: 210,311,517,614,701  B2: Tom
A3: 51,120,210           B3: Sam
A4: 311,701,1012         B4: Tim

在选项卡2上，我有一列数字. 示例:

On tab 2, I have a column of numbers. Example:

A1: 131
A2: 210
A3: 120
A4: 42

在每个数字旁边，我想返回与包含该数字的第一个列表关联的名称.所以在这个例子中，应该是...

Next to each number, I want to return the name associated with the first list which contains the number. So in this example, it should be...

A1: 131  B1: Jim
A2: 210  B2: Jim
A3: 120  B3: Sam
A4: 42   B4: N/A

我在想可能涉及到find或vlookup，但我一直无法弄清楚如何将其组合在一起.

I'm thinking find or vlookup might be involved, but I haven't been able to figure out how to make it come together.

推荐答案

为了实现真正的匹配，您必须强制执行以下条件:所寻找的每个值都将以逗号开头和结尾.此字符串串联起来加上通配符将需要一个 array公式.

In order to achieve a true match, you must enforce the condition that each value looked for will begin and end with a comma. This string concatenation together with the wildcards will necessitate an array formula.

E1中的数组公式为=INDEX('Tab 1'!$B$1:$B$4,MATCH("*,"&D1&",*",","&'Tab 1'!$A$1:$A$4&",",0)).这需要 Ctrl + Shift + Enter 而不是简单的 Enter .输入正确后，可以根据需要填写.您可能希望应用IFERROR包装器来显示一个空字符串，而不是os #N/A.

The array formula in E1 is =INDEX('Tab 1'!$B$1:$B$4,MATCH("*,"&D1&",*",","&'Tab 1'!$A$1:$A$4&",",0)). This requires Ctrl+Shift+Enter rather than simply Enter. Once entered correctly, it can be filled down as necessary. You may wish to apply an IFERROR wrapper to show an empty string instead os #N/A.

这篇关于在一个单元格范围内找到一个值作为子字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！