我希望创建一个公式，使我能够为8位数字搜索一组数据。我附上了测试表。
在B列中是我需要在一组数据中找到的库存号码。
在E中，E是要检查的完整列表，但是，在任何一个单元格中，您都可以拥有几个不同的8位库存编号。
在我的测试表中，我发现Vlookup仅搜索第一个库存号码，而不是该单元格中的所有库存号码。我尝试了一个索引匹配公式与转孔拆分相结合，但是这些都没有使我能够正确搜索我的数据集。
= arrayformula（vlookup（b2：b，$ e $ 2：$ e，1,0））
然后
= index（$ e $ 2：$ e $ e $ 1139拆分（$ e $ 2：$ e，“;”）））））））
数据可以用空格或“”或“;”在单元格内分开。
您应该能够使用此链接来制作我的测试表的副本：
希望您能提供帮助。
I am looking to create a formula that enables me to search a set of data for an 8 digit number. I have attached my testing sheet.
In column B is the stock numbers I need to find within a set of data.
In column E is the full list to be checked against, however, within any one cell, you can have several different 8 digit stock numbers.
In my test sheet, I have found that a vlookup only searches for the first stock number and not all the stock numbers within that cell. I have attempted an index match formula combined with a transpose split however none of these has enabled me to properly search my data set.
=ARRAYFORMULA(VLOOKUP(B2:B,$E$2:$E,1,0))
then
=INDEX($E$2:$E$1139,(MATCH(B2,TRANSPOSE(SPLIT($E$2:$E,";, ")))))
The data can be separated within the cell with either spaces or "," or ";"
You should be able to make a copy of my test sheet using this link:
https://docs.google.com/spreadsheets/d/1UckPZw6tWBw0H13p79N94IqV2EFyfhYNyEhmLecr3XM/copy#gid=55962883
Hope you can help.
发布评论
评论（1）
尝试：
更新：
获取true/false用途：
或者如果要返回匹配或n/a用途：
try:
update:
to get true/false use:
or if you want to return match or N/A use: