如何编写一个Excel公式以根据单元格的内容返回范围内的值? [英] How can I write an excel formula to return a value from a range based on the contents of a cell?

问题描述

如何编写一个Excel公式来检查一个单元格是否包含一个单独范围内列出的单词，然后返回指定范围内的单个单词?

How can I write an Excel formula which will check if a cell contains a word listed in a separate range, and then return the single, individual word from the specified range?

公式必须指定单元格和范围，而不是将值填入公式本身.

The formula must specify the cell and the range, rather than cramming the values into the formula itself.

示例:

例如，该公式正写在单元格B1中，并引用了单元格A1.单元格A1包含文本英国汽车保险".范围是从单元格C1到单元格C10(C1:C10).在此范围内，列出了10个国家/地区，每个单元格向下层叠(例如C1 ="uk"，C2 ="usa"，C3 ="france等).

For example, the formula is being written in cell B1 and references the cell A1. The cell A1 contains the text "uk car insurance". The range is located from cells C1 to cell C10 (C1:C10). In this range 10 countries are listed, one per cell cascading downwards (e.g: C1="uk", C2="usa", C3="france" etc).

基于此示例，B1中的公式应从单元格范围(C1:C10)中拉回"uk"值，因为A1中的单元格包含包含"uk"的文本"uk car insurance". uk的文本也存储在单元格C1中，因此应将其拉回到B1中.

Based on this example, the formula in B1 should pull back the value "uk" from the range of cells (C1:C10) as the cell in A1 contains the text "uk car insurance" which contains "uk". The text of "uk" is also stored in cell C1, so that's what should be pulled back into B1.

这样的公式(现在不再使用我们的示例了)不适用于我的目的:

A formula like this (moving away from our example now) wouldn't work for my purposes:

=IF(COUNTIF(L5:L47,"FALSE"),"Portfolios Mismatched","Portfolios Matched")

我在C列中的国家范围可能会变得很大，因此任何试图在其内部列出值(而不是引用范围)的公式都无法扩展.

My range of countries in column C may become vast and thus any formula which attempts to list the values within itself (instead of referring to a range) is not scalable.

推荐答案

使用INDEX和AGGREGATE:

Use INDEX and AGGREGATE:

=INDEX(C:C,AGGREGATE(15,6,ROW($C$1:INDEX(C:C,MATCH("zzz",C:C)))/(ISNUMBER(SEARCH(" " & $C$1:INDEX(C:C,MATCH("zzz",C:C)) & " "," " & A1 & " "))),1))

$C$1:INDEX(C:C,MATCH("zzz",C:C))设置范围，它是动态的.它将范围设置为仅具有值的范围，并随着在参考列表中添加或删除值而更改.

The $C$1:INDEX(C:C,MATCH("zzz",C:C)) sets the range, it is dynamic. It will set the range to only those with values and change as values are added or removed from the reference list.

SEARCH将搜索匹配项. " " &和& " "确保我们正在寻找整个单词，因此我们不会对eric和erica之类的东西产生误报.

The SEARCH will search for matches. The " " & and & " " make sure we are looking for the entire word, So we do not get false positives on things like eric and erica.

如果在参考单元格中找到一个单词，聚合将找到第一行.

The Aggregate will find the first row in which a word if found in the reference cell.

索引返回该单词作为答案.

Index returns that word as the answer.

这篇关于如何编写一个Excel公式以根据单元格的内容返回范围内的值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！