Returning Results Based on Two Criteria

A

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
 
A

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!
 
B

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
 
A

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.
 
B

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
 
D

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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top