Lookups with Multiple Non-Exact Criteria using INDEX-MATCH - Problem finding nearest values that best meet conedition

Question

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))

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.

For example.

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"

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?

Dataset table looks like this:

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

What you are finding here is that the VLOOKUP/HLOOKUP and MATCH Range/Inexact lookups rely on the list being ordered. — Chronocidal

Terry W Terry W · Accepted Answer · 2019-09-06T12:49:52

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)

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.

EDIT #2

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

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:

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

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),"")

EDIT #3

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))}

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.

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 :)

Lookups with Multiple Non-Exact Criteria using INDEX-MATCH - Problem finding nearest values that best meet conedition

2 Answers