Index Match - Multiple Criteria with Great Than

J

Josh123

New Member
Joined
Dec 11, 2013
Messages
18
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]
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Sort by date Sort by votes
@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?
 
Upvote 0
@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]
 
Upvote 0
@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 *")
 
Upvote 0
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.
 
Upvote 0
ErikTyler said:
@Peter, I had the same question, so I just included an IFERROR( ,"* NONE *") wrapper.
Click to expand...
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.
 
Upvote 0
@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 *")
 
Upvote 0
@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]
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
SUM of Columns based on multiple criteria with User Form
2
Replies
15
Views
363
Alex Blakenburg
A
A
  • Question Question
Excel - looking up data with multiple criteria from table/array, date must be same or nearest before the target cell
Replies
8
Views
627
Peter_SSs
Peter_SSs
A
  • Solved
Index Match With Multiple Criteria
Replies
3
Views
318
acool
A
J
  • Solved
INDEX-MATCH-MATCH help!
2
Replies
15
Views
297
henrik2h
H
M
  • Solved
Index and Match
Replies
3
Views
133
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top