Returning Results Based on Two Criteria

[alanstad]

I need to return a result based on two criteria. When matching each of
the criteria, there will often not be exact matches. So, the match
should be one the criteria value that most closely is less than or
equal to value passed. Once a match is found on the first criteria,
the second one only looks the subset of records where that first
criteria matches. (Hopefully I am not being too confusing).

Example 1: Criteria 1 = .5, Criteria 2 = 7. The Result returned would
be .2.

Example 2: Criteria 1 = .1, Criteria 2 = 4. The Result returned would
be .25.

Criteria 1 Criteria 2 Result
0 0 0
0 5 0.2
0 10 0.4
0.1 0 0.25
0.1 5 0.45
0.1 10 0.65
0.2 0 0.47
0.2 5 0.67
0.2 10 0.87

What functions should I be looking at to do this in Excel?

Thank you,

Alan

 

[Biff]

Hi!

Will there *ALWAYS* be an exact match of the first criteria?

Biff

 

[alanstad]

In most cases, there will not be an exact match. When that occurs, the
logic would use the closest value that is less than the criteria.
Example 1 about attempts to illustrate this.

Thank you!

 

[Biff]

Ok, let's see.......

Your first example is:

Criteria 1 = .5, Criteria 2 = 7. The Result returned would be .2.

The first column of the table is in ascending order starting at 0 and
progressing to 0.2.

The first criteria is .5 (0.5) so wouldn't 0.2 be the max that is less than
or equal to 0.5?

With my understanding of your explanation I would think the result for:

Criteria 1 = 0.5
Criteria 2 = 7

should be: 0.67

Biff

 

[alanstad]

You're right. I meant for my first example to have a criteria of .05,
not .5. But I did not type what I was thinking.

Sorry for the confusion.

 

[Biff]

Try this:

Assume the table is in the range A1:C9

E1 = criteria 1 = .05
F1 = criteria 2 = 7

Formula entered as an array using the key combo of CTRL,SHIFT,ENTER:

=INDEX(C1:C9,MATCH(1,(A1:A9=MAX(IF(A1:A9<=E1,A1:A9)))*(B1:B9=MAX(IF(B1:B9<=F1,B1:B9))),0))

Biff

 

[Domenic]

Here's another way...

=LOOKUP(F1,INDEX(B1:B9,MATCH(E1,A1:A9)-2):INDEX(C1:C9,MATCH(E1,A1:A9)))

....where E1 contains your first criterion, such as .05, and F1 contains
your second criterion, such as 7.

Hope this helps!

 

[alanstad]

Thank you!