Index Match - Multiple Criteria with Great Than

[Josh123]

Hi,

In this scenario, I need a formula to return a specific value based on (1) a criteria with an exact match, and (2) a range criteria where there is not always an exact match.

• Table 1 contains the criteria of the lookup (Year and Number), as well as the value I want the formula to return (Score).
• Table 2 is the dataset containing the Score value along with the associated criteria.

Criteria 1

• The first criteria for the lookup is based on the Year field.
• The year in Table 1 needs to match the year in Table 2.

Criteria 2 and 3

• The second and third criteria for the lookup is based on the Number field in Table 1 and the Minimum and Maximum fields in Table 2.
• The Number value must be greater than or equal to the Minimum field values and must be less than or equal to the Maximum field values.

The values in the Minimum and Maximum fields may overlap, so the criteria must take into consideration both fields for a good match.

Table 1
[TABLE="class: grid, width: 300, align: left"]
<tbody>[TR]
[TD="align: center"]Year[/TD]
[TD="align: center"]Number[/TD]
[TD="align: center"]Score[/TD]
[/TR]
[TR]
[TD="align: center"]2017[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]3[/TD]
[/TR]
</tbody>[/TABLE]




Table 2
[TABLE="class: grid, width: 400, align: left"]
<tbody>[TR]
[TD="align: center"]Criteria 1

---

Year[/TD]
[TD="align: center"]Criteria 2

---

Minimum[/TD]
[TD="align: center"]Criteria 3

---

Maximum[/TD]
[TD="align: center"]Return Value

---

Score[/TD]
[/TR]
[TR]
[TD="align: center"]2016[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]2016[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]2017[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]3[/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

I also found that Erik's formula from post #8 (once the ranges were adjusted) produced the results you have given in your latest sample - see column D below.
A caveat though - Erik's formula is not robust against row insertion. If you have a sheet set up as below and begin inserting new rows at the top of the sheet you will see the formulas in column D begin to fail progressively from the bottom up.

@Peter_SSs,

It will not be possible that no match will be found.

Given this, you could also try the shorter & more robust formula in C2 below.
My formula does also rely on the years being in ascending order and the Minimums in each year also being in ascending order, but that appears to be the case from your sample.
[If any number in column B or minimum value in column F could be greater than or equal to 1,000,000 then the power of 10 in the formula (currently 6) would need to be increased]

Excel 2016 (Windows) 32 bit
	A	B	C	D	E	F	G	H
1	Year	Number	Score (Peter)	Score (Erik)	Year	Minimum	Maximum	Score
2	2016	0	1	1	2016	0	5	1
3	2016	100	5	5	2016	5	10	2
4	2017	18	9	9	2016	10	15	3
5	2017	100	10	10	2016	15	20	4
6					2016	20	999	5
7					2017	0	5	6
8					2017	5	10	7
9					2017	10	15	8
10					2017	15	20	9
11					2017	20	999	10
Lookup Value

Cell Formulas
Range	Formula
C2	=LOOKUP(A2*10^6+B2,E$2:E$11*10^6+F$2:F$11,H$2:H$11)
D2	=INDEX($H$2:$H$11, SUMPRODUCT(MAX(($E$2:$E$11=A2)*($F$2:$F$11<=B2)*($G$2:$G$11>=B2)*(ROW($E$2:$E$11))))-1)

 

[inactiveuserps07]

Josh, since your new data has all upper limits accounted for and you've stated there will be no occasions of "NOT FOUND," the formula I suggested can be shortened:

=INDEX($H$2:$H$11, SUMPRODUCT(MAX(($E$2:$E$11=A4)*($F$2:$F$11<=B4)*(ROW($E$2:$E$11))))-1)

If, for some reason, you didn't want your headers in Row 1, yes, the final "-1" would need to be a minus sign and then the number of your header row.

 

[Josh123]

Peter_SSs and ErikTyler,

I have tested both of your formulas and I am happy to say that they are both working perfectly. Thank you so much for all your help with this formula - I truly appreciate it!

ErikTyler - I think I know why I was unsuccessful with using your first two formulas. I am not too familiar with how the ROW formula works and it may have been that the "-1" at the end of the formula may have been incorrect in my specific setup since I am truly using the table features in Excel (just a guess). However, after converting them all to ranges and then simplifying the structure, the formula worked just fine. I apologize for the confusion on my end.

Thank you!

 

[inactiveuserps07]

Josh, if your two tables are formatted as tables (called Table1 and Table2, no spaces), and you aren't frightened of structured references, this would be the fastest and most flexible, always adapting exactly to the size of your tables:

=INDEX(Table2[Score], SUMPRODUCT(MAX((Table2[Year]=[@Year])*(Table2[Minimum]<=[@Number])*(Table2[Score]))))

You could then move the tables anywhere without a problem as well, since the formula relies solely on the table names and column header names, not sheet rows or columns.

Peter's formula in structured reference format would look like this:

=LOOKUP([@Year]*10^6+[@Number],Table2[Year]*10^6+Table2[Minimum],Table2[Score])

General rules of structured referencing:

1. table_name[whole column name]

2. table name is not necessary for formulas in the current table.

3. the current table can add @ to the column name to mean "IN THIS ROW" (e.g., [@Year] means the value in the cell of this row under the Year header)

 

[Peter_SSs]

Josh, if your two tables are formatted as tables (called Table1 and Table2, no spaces), and you aren't frightened of structured references, this would be the fastest and most flexible, always adapting exactly to the size of your tables:

=INDEX(Table2[Score], SUMPRODUCT(MAX((Table2[Year]=[@Year])*(Table2[Minimum]<=[@Number])*(Table2[Score]))))

Sorry, but again this is not robust.
For example, using the table data as shown in post #9 & your formula in Table1, Score column ..
Change the second score in Table2 from 2 to 8 and look at the Table1 result for 2016, 100.
Now change that same cell in Table2 from 8 to 200 & look at the same result.

In fact, that formula will only work if the Score column in Table2 starts at 1 and increments by exactly 1 each row.

The formula that I posted in post #11, or as adapted to structured referencing by Erik in post #14 (thanks Erik) does not have that Score restriction.

Thank you so much for all your help with this formula - I truly appreciate it!

You are very welcome.

 

[inactiveuserps07]

@Peter, you stated above that my structured reference formula "will only work if the Score column in Table2 starts at 1 and increments by exactly 1 each row."

To the best of my knowledge, that statement is incorrect. The only stipulation would be that the scores per year increment, regardless of the amount.

As for incremented ranges jumping around from 1 to 8 to 3 ... based on the OP's data structure and most like it, this didn't seem a likelihood.

And if the scores decreased as ranges increased, you'd simply change MAX to MIN.

Open to hearing why you think this is not the case, since I'm certainly not beyond missing something.

 

[inactiveuserps07]

@Peter, ignore the above post!

Of course it would rely on increments by 1, because of the nature of INDEX.