Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
 
Posts: n/a
Default 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

  #2   Report Post  
Biff
 
Posts: n/a
Default Returning Results Based on Two Criteria

Hi!

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

Biff

wrote in message
oups.com...
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



  #3   Report Post  
 
Posts: n/a
Default Returning Results Based on Two Criteria

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!

  #4   Report Post  
Biff
 
Posts: n/a
Default Returning Results Based on Two Criteria

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

wrote in message
ups.com...
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!



  #5   Report Post  
 
Posts: n/a
Default Returning Results Based on Two Criteria

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.



  #6   Report Post  
Biff
 
Posts: n/a
Default Returning Results Based on Two Criteria

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

wrote in message
oups.com...
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.



  #8   Report Post  
 
Posts: n/a
Default Returning Results Based on Two Criteria

Thank you!

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
format cell based on results of vlookup function Edith F Excel Worksheet Functions 1 July 21st 05 07:39 PM
criteria 1(a,b,c), criteria 2 (T,F) - Results (3 answers) achievab Kikkoman Excel Discussion (Misc queries) 5 July 1st 05 11:05 PM
How do I do multi VLOOKUP's based on certain criteria per cell? Milky_UK Excel Worksheet Functions 3 June 17th 05 05:51 PM
counting cells (COUNTIF) based on two or more criteria Tricia S. Excel Worksheet Functions 10 March 17th 05 03:17 PM
returning a value based on mulitple criteria Brad Excel Worksheet Functions 6 December 31st 04 09:14 AM


All times are GMT +1. The time now is 12:59 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"