MS Excel LOOKUP从顶部到底部 [英] MS Excel LOOKUP from top to bottom column

问题描述

我在寻找LOOKUP从上至下搜索的方式时遇到了问题.这是情况.我需要为Excel创建公式以在句子中搜索硬件零件.

I have a problem finding a way for LOOKUP to search from top to bottom column. Here's the situation. I need to create a formula for Excel to search for hardware part inside a sentences.

示例:

句子(A1)

Sentence (A1)

客户PC无法打开.检查主板.主板故障.更换主板. PSU功率不足.更换PSU.

Customer PC can't turn on. Check motherboard. Motherboard faulty. Replace motherboard. PSU don't have enough power. Replace PSU.

这是查找范围列(硬件)

Here is lookup range column(HARDWARE)

• 替换PSU
• 更换主板

结果范围列(HARDWARE_PART)

result range column(HARDWARE_PART)

• PSU
• 主板

我使用的公式是

LOOKUP(9999;SEARCH(HARDWARE;A1);HARDWARE_PART)

结果将是主板".因为LOOKUP将从下至上搜索列.

The result will be "motherboard". Because LOOKUP will search the column from bottom to top.

我现在要做的是让LOOKUP从上到下搜索该列，以便它可以检测到另一个替换零件.

What I gonna do right now is for LOOKUP to search the column from top to bottom so that it can detect another replace part.

推荐答案

使用命名范围是一个好步骤；动态命名范围甚至更好.但是，它们会产生混乱，试图将正确的相对行号返回给

Using named ranges is a good step; dynamic named ranges are even better. However, they present a problem by generating confusion trying to return the correct relative row number to an INDEX function when they do not start in the first row.

我的两个命名范围(例如HARDWARE和HARDWARE_PART)使用指的是:公式，

My two named ranges (e.g. HARDWARE and HARDWARE_PART) used Refers to: formulas as,

=Sheet1!$AA$2:INDEX(Sheet1!$AA:$AA, MATCH("zzz",Sheet1!$AA:$AA))
=Sheet1!$AB$2:INDEX(Sheet1!$AB:$AB, MATCH("zzz",Sheet1!$AA:$AA))

由于返回的行从第二行开始，因此必须将返回的行修改为 1 .

This necessitated modifying the returned row by 1 since they started in the second row.

B1中的标准公式为

=IFERROR(INDEX(HARDWARE_PART, AGGREGATE(15, 6, (ROW(HARDWARE)-1)/ISNUMBER(SEARCH(HARDWARE, $A1)), COLUMN(A:A))), "")

以降序优先顺序继续进行后续比赛.

Fill right for subsequent matches in descending priority.

IFERROR函数包装器"可以通过将错误返回值替换为零长字符串来避免在不匹配项上显示#NUM!错误.

An IFERROR function 'wrapper' can avoid showing #NUM! errors on non-matches by substituting the error return for a zero-legth string.

如果您想取消优先级，请换出 15 ( 14 的-4F17-92B6-E19993FA26DF"rel =" nofollow noreferrer>AGGREGATE¹选项(

If you want to reverse the priority, swap out the 15 (SMALL function) AGGREGATE¹ option for 14 (LARGE function).

xl2010以前的附录

对于不能使用AGGREGATE函数的Excel版本，此标准的非CSE公式将结果重复.在B1中，

For versions of Excel that cannot use the AGGREGATE function, this standard non-CSE formula duplicates the results. In B1 as,

=IFERROR(INDEX(HARDWARE_PART, SMALL(INDEX(ROW(HARDWARE)-1+ISERROR(SEARCH(HARDWARE, $A1))*1E+99, , ), COLUMN(A:A))), "")

---

¹ Excel 2010中引入了AGGREGATE函数.在早期版本中不可用.

---

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

这篇关于MS Excel LOOKUP从顶部到底部的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！