使用INDEX-MATCH的具有多个非精确条件的查找-查找最符合条件的最接近值的问题 [英] Lookups with Multiple Non-Exact Criteria using INDEX-MATCH - Problem finding nearest values that best meet conedition

问题描述

我正在尝试使用INDEX-MATCH使用多个非精确条件进行查找. 形式如下:

I am trying to make lookups with Multiple Non-Exact Criteria using INDEX-MATCH. The formual looks like this:

= INDEX(C314:C318; MATCH(1;(D314:D318> = G313)*(E314:E318> = G314); 0))

=INDEX(C314:C318;MATCH(1;(D314:D318>=G313)*(E314:E318>=G314);0))

条件是:大于或等于X.

Criterias are: greater or equal to amount X.

公式工作正常，但是当使用一长串值时，它找不到最佳匹配值，而是找到与条件匹配的第一个值.

Formula works fine, however when using a long list of values, it does not find the best matching value, it finds the first value that matches the criteria.

例如.

条件1为:代码找到等于2055516的代码" 条件2为:数值等于或大于77的值" 条件3为:字母等于或大于H的字母"

Condition 1 is: code "find code equal to 2055516" Condition 2 is: numerical "find value equal or above 77" Condition 3 is: alphabetical "find letter equal or greater than H"

在我有很多值的大型数据集中，它仅找到与该条件匹配的下一个最佳值.满足该条件的第一个值将是"80"和"R"，但是，按照下面的方式在我的数据集中使用以下值，则可以更好地满足这些条件，即"78"和"I".我想这里的问题很清楚.

In a large dataset where I´ve got many values, it finds only the next best value that matches this criteria. First value that meet that condition would be "80" and "R", however, following values in my dataset, way below, meet much better those criteria with "78" and "I". Problem here is clear I guess.

我该如何调整公式以寻找更适合我的条件的值?

How can I adapt my formula to look for those much more fitting values that meet my condtions?

数据集表如下:

该公式应返回最佳加工产品的名称"A，B，C，D，E".

The formula should return the name "A, B, C, D, E" of the best maching product.

推荐答案

我使用了一个名为Helper的帮助器列，它首先使用以下公式按字母顺序对Condition 2中的字母进行排名(将其向下拖动以应用于所有行) :

I used a helper column called Helper to rank the letters in Condition 2 alphabetically first using the following formula (drag it down to apply to all rows):

=COUNTIF(Condition_2,"<="&Condition_2)

然后使用以下公式找到最佳匹配项(尽管它是一个数组公式，不需要由Ctrl+Shift+Enter确认):

then use the following formula to find the best match (although it is an array formula it does not need to be confirmed by Ctrl+Shift+Enter):

=INDEX(Product1,MATCH(AGGREGATE(15,6,Helper/((Condition_1>=77)/(Condition_2>="H")),1),Helper,0))

将上面公式中的命名范围替换为工作表中的实际范围.

Replace the named ranges in the above formula with the actual ranges in your worksheet.

在所有公式中用;作为分隔符替换,，以适合您的系统.

Replace , with ; as the delimiter in all formulas to suit your system.

编辑#2

基于新方案，仅在查找值为数字(EAN)

Based on the new scenario, the problem can be solved by AGGREGATE function solely given that the look up value is a number (EAN)

我上面的示例的单元格J2中的公式为:

The formula in Cell J2 of my above example is:

=AGGREGATE(15,6,EAN/((DIMENSION=F2)/(LOAD_INDEX>=G2)/(SPEED_INDEX>=H2)),1)

请注意，以下是所有命名范围，需要将其替换为工作簿上的实际范围:

Please note the following are all named ranges which needs to be replaced with the actual range on your workbook:

• 尺寸为B2:B8
• LOAD_INDEX 为C2:C8
• SPEED_INDEX 为D2:D8
• EAN 为A2:A8

• DIMENSION being B2:B8
• LOAD_INDEX being C2:C8
• SPEED_INDEX being D2:D8
• EAN being A2:A8

如果您不希望因没有匹配结果而显示错误#NUM!，则可以使用 IFERROR 返回一个空白单元格，如我的示例的单元格J3所示.公式是:

If you do not want to show the error #NUM! for no matching result, you can use IFERROR to return a blank cell as shown in Cell J3 of my example. The formula is:

=IFERROR(AGGREGATE(15,6,EAN/((DIMENSION=F3)/(LOAD_INDEX>=G3)/(SPEED_INDEX>=H3)),1),"")

编辑#3

请使用以下数组公式(需要通过按 Ctrl + Shift + Enter 进行确认)来找到LOAD INDEX和SPEED INDEX与Helper列的帮助.

Please use the following array formula (need to confirm by pressing Ctrl+Shift+Enter) to find the closest match of LOAD INDEX and SPEED INDEX with the help of a Helper column.

{=INDEX(EAN,MATCH(AGGREGATE(15,6,Helper/((LOAD_INDEX/((DIMENSION=G2)/(LOAD_INDEX>=H2)/(SPEED_INDEX>=I2)))=AGGREGATE(15,6,LOAD_INDEX/((DIMENSION=G2)/(LOAD_INDEX>=H2)/(SPEED_INDEX>=I2)),1)),1),Helper/((LOAD_INDEX/((DIMENSION=G2)/(LOAD_INDEX>=H2)/(SPEED_INDEX>=I2)))=AGGREGATE(15,6,LOAD_INDEX/((DIMENSION=G2)/(LOAD_INDEX>=H2)/(SPEED_INDEX>=I2)),1)),0))}

逻辑是首先找到与LOAD INDEX最接近的匹配，然后从与LOAD INDEX最接近的范围中找到与SPEED LIMIT最接近的匹配.

The logic is to first find the closest matches to LOAD INDEX and then find the closest match to SPEED LIMIT from the range with the closest matches to LOAD INDEX.

如果不想因没有匹配结果而显示#NUM!错误，也可以使用 IFERROR 返回所需结果.

Again if you do not want to show #NUM! error for no matching result, you can use IFERROR to return the desired result.

让我知道是否有任何问题.欢呼声:)

Let me know if there is any question. Cheers :)

这篇关于使用INDEX-MATCH的具有多个非精确条件的查找-查找最符合条件的最接近值的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！