Returning Results Based on Two Criteria

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

Hi!

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

Biff

<[email protected]> wrote in message
news:[email protected]...
>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
>

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!

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

<[email protected]> wrote in message
news:[email protected]...
> 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!
>

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.

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

<[email protected]> wrote in message
news:[email protected]...
> 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.
>

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!

In article <[email protected]>,
[email protected] wrote:

> 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

Thank you!