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]

 

[inactiveuserps07]

@Josh, it's not clear what should be done with the overlapping ranges (which is very odd, by the way). For instance, if your Table-1 data had the Year 2016 and the Number 7, what would you expect the score to be — 1 or 2 — and how would you make that determination?

 

[Josh123]

@ErikTyler,

After reviewing your simulation, I have realized the criteria rules for the range need to be modified - thank you.

Revisions:

• Disregard the Maximum field (criteria 3)
• The Number in Table 1 must be greater than or equal to the value in the Minimum field (assuming the years match)

Please use this revised table for this same example:

Table 1 (revised)
[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"]2016[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]1[/TD]
[/TR]
</tbody>[/TABLE]

 

[inactiveuserps07]

@Josh123, OK, I've worked it out so that the score will be retrieved from the maximum range per year.

My formula assumes the following:

1. That your "Table 1" headers are in A1:C1.

2. That your "Table 2" headers are in E1:H1.

Given that setup, this formula would go in C2 (and then be drag-copied down if/as needed):

=IFERROR(INDIRECT("H"&SUMPRODUCT(MAX(($E$2:$E$4=A2)*($F$2:$F$4<=B2)*($G$2:$G$4>=B2)*(ROW($E$2:$E$4))))),"* NONE *")

 

[Peter_SSs]

Josh123

Is it possible that no match could be found? For example, in post #1 what if Table 1 had 2017 and the number 3? I ask, because the minimum value shown in Table 2 for 2017 is 5.

 

[inactiveuserps07]

@Peter, I had the same question, so I just included an IFERROR( ,"* NONE *") wrapper.

 

[Peter_SSs]

@Peter, I had the same question, so I just included an IFERROR( ,"* NONE *") wrapper.

Fair enough Erik, but I'm considering the possibility of a much simpler formula that also avoids the volatile function INDIRECT. I'm also trying to avoid the potential problem of having to edit the formulas that would arise with your formula if the user subsequently decides to insert, say, a new column A.

The answer to the question I've asked is relevant to what I have in mind.

 

[inactiveuserps07]

@Peter, I will look forward to seeing what you come up with. Always enjoy learning something new.

In the meantime, an adaptation of my formula without INDIRECT():

=IFERROR(INDEX($H$2:$H$4, SUMPRODUCT(MAX(($E$2:$E$4=A2)*($F$2:$F$4<=B2)*($G$2:$G$4>=B2)*(ROW($E$2:$E$4))))-1),"* NONE *")

 

[Josh123]

@Peter_SSs,

It will not be possible that no match will be found. The Minimum field will always contain a value that is equal to or less than the Number value in Table 1 for each year. The largest value in the Minimum field in the dataset will contain a value which could be less than or equal to the Number value (but the corresponding Maximum value in that same row will be an "infinite" value because it will be quite large). So in the event the Number value is larger than the largest value in the Minimum field, the formula should return the Score value in that largest Minimum value row.


@ ErikTyler,

I have applied your formula to my dataset, but it doesn't seem to be working. The formula return is always pulling the Score value associated with the smallest value in the Minimum field from the largest Year value.

I have revised Tables 1 and 2 for additional clarification:

[TABLE="class: grid, width: 300, align: left"]
<tbody>[TR]
[TD]

A​

---

Year​

[/TD]
[TD]

B​

---

Number​

[/TD]
[TD]

C​

---

Score​

[/TD]
[/TR]
[TR]
[TD="align: center"]2016[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]2016[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]5[/TD]
[/TR]
[TR]
[TD="align: center"]2017[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]9[/TD]
[/TR]
[TR]
[TD="align: center"]2017[/TD]
[TD="align: center"]100[/TD]
[TD="align: center"]10[/TD]
[/TR]
</tbody>[/TABLE]











Table 2 (Revised)
[TABLE="class: grid, width: 400, align: left"]
<tbody>[TR]
[TD]

E​

---

Year​

[/TD]
[TD]

F​

---

Minimum​

[/TD]
[TD]

G​

---

Maximum​

[/TD]
[TD]

H​

---

Score​

[/TD]
[/TR]
[TR]
[TD="align: center"]2016[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]2016[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]2016[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]2016[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]4[/TD]
[/TR]
[TR]
[TD="align: center"]2016[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]999[/TD]
[TD="align: center"]5[/TD]
[/TR]
[TR]
[TD="align: center"]2017[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]6[/TD]
[/TR]
[TR]
[TD="align: center"]2017[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]7[/TD]
[/TR]
[TR]
[TD="align: center"]2017[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]8[/TD]
[/TR]
[TR]
[TD="align: center"]2017[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]9[/TD]
[/TR]
[TR]
[TD="align: center"]2017[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]999[/TD]
[TD="align: center"]10[/TD]
[/TR]
</tbody>[/TABLE]

 

[inactiveuserps07]

Hey, Josh. I entered your data from the new tables above, and got all the same answers you have using the formula I provided. So something is fishy.