如果字符串包含来自列表的单词，则返回值与列表值相邻 [英] If string contains word from list, return value adjacent to list value

问题描述

目前我有一组单元格，每个单元都有一个唯一的标识符。我还有这些唯一标识符的列表，以及每个标识符对应的值。

我想做的是找到一个单元格包含的标识符，然后输出相应的值，下面是一个例子：

B3中的公式是

  = INDEX（F $ 3：F $ 5，积分（15,6，ROW（$ 1：$ 3）* SIGN（*& E $ 3：E $ 5&*，A3,0）），1））
  

请注意， ROW（1：3）是 F3：F5，而不是工作表上的实际行号。我还在原始的交叉引用表中加了查找和插入值，以避免相关查找匹配的感觉。

---

¹ _{AGGREGATE函数与Excel 2010一起引入。它在早期版本中不可用。}

Currently I have a set of cells, and each has, among useless information, a unique identifier. I also have a list of these unique identifiers, as well as what value each identifier corresponds to.

What I would like to do is find which, if any, identifier a cell contains, and then output the corresponding value, below is an example:

http://i.stack.imgur.com/97aKI.png

So where the cell contains "ADC", I would like excel to find where ADC comes up in the reference array, and then return the corresponding value.

If this can be done with a formula or a macro, either would be great. I have tried fiddling with index, match, and search, in various combinations, but nothing seems to be working. I have found creating a massive if statement to be impractical as there are about 70 unique values to search for.

Any suggestions would be welcome!

edit: I was recommended to use vlookup, but I am not looking for an identical match, but instead for a specific value contained within a string. If vlookup does have this functionality then could somebody show me how to put this into practice with my specific example?

解决方案

One method of a 'reverse-wildcard' lookup can be achieved is with the newer AGGREGATE¹ function. This function can produce cyclic calculation and has an option (e.g. 6) to discard errors. Use this to produce a row number on the match to the cross-reference table with the INDEX function returning the actual value.

      

The formula in B3 is,

=INDEX(F$3:F$5, AGGREGATE(15, 6, ROW($1:$3)*SIGN(MATCH("*"&E$3:E$5&"*", A3, 0)), 1))

Note that ROW(1:3) is the position within F3:F5, not the actual row number on the worksheet. I've also scrambled the Find and Insert values in your original cross-reference table to avoid the perception of an associative lookup match.

---

¹ _{The AGGREGATE function was introduced with Excel 2010. It is not available in earlier versions.}

这篇关于如果字符串包含来自列表的单词，则返回值与列表值相邻的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！