Excel:具有部分编号的INDEX MATCH [英] Excel: INDEX MATCH with partial number

问题描述

我有一张12位数字和相关信息的大桌子

I have a large table of 12 digit numbers and associated info

我有一个由10位和11位数字组成的小列表(第一位和/或最后一位数字被截断了)-我试图越过这两个列表以识别小列表上的项目

I have a small list of 10 and 11 digit numbers (the first and/or last digits were cut off) - I'm attempting to cross these two lists to identify the items on the small list

通常，我会使用索引匹配将相关信息从表中移出列表，但是由于今天列表中只有部分数字，因此无法使用公式

normally, I'd use an index match to bring the associated info out of the table into the list, but because today I have only partial numbers in my list, I can't get the formula to work

我在这里看到了其他示例，这些示例搜索范围内包含的部分文本字符串，但是我无法将这些公式调整为我的数据.通配符似乎不适用于数字.

I've seen other examples here that search for partial text strings contained within a range, but I haven't been able to adapt those formulas to my data. wildcards don't seem to work with numbers.

非常感谢您的输入，如果无法在网站上找到现有的解决方案，请提前致歉.

Many thanks for your input, and apologies in advance if I failed to find an existing solution on the site.

推荐答案

要匹配数字范围内的部分数字，就像处理字符串一样，可以通过组合一个临时数组来转换INDEX/MATCH来使用数组公式，并使用INDEX/MATCH数字变成字符串.

To match partial numbers inside a number range, like you do with strings, you can use an array formula with INDEX/MATCH, by composing a temporary array that converts the numbers into strings.

说一列A是您的12位数字列，并且您想匹配序列1234567890并从B列中检索值，此 CSE公式起作用:

Say column A is your 12 digit numbers column, and you want to match the sequence 1234567890 and retrieve the value from column B, This CSE formula works:

=INDEX($B$2:$B$9999, MATCH("*1234567890*",""&$A$2:$A$9999,0))

Ctrl Shift Enter

尽管您可以使用完整的列A:A和B:B，但应尽量避免使用数组公式，因为它们很慢.全列表示超过一百万个条目的计算和操作数组，因此请避免使用.还要注意从数字到字符串的昂贵"转换($A$2:$A$9999中的所有数字都在这里转换为字符串).

Although you can use full columns A:A and B:B, this should be avoided as much as possible with array formulas, because they're slow. Full columns mean computing and operating arrays of more than a million entries, so avoid it. Also note the "expensive" conversion from numbers to strings (all numbers in the $A$2:$A$9999 are converted to strings here).

要使用单元格引用(例如D2)而不是经过编码的1234567890，应使用如下公式:

To use a cell reference, say D2, instead of the harcoded 1234567890, the formula should be used like this:

=INDEX($B$2:$B$9999,MATCH("*"&D2&"*",""&$A$2:$A$9999,0))

这篇关于Excel:具有部分编号的INDEX MATCH的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！