Solved How can i find the correct item using a max rating

February 22, 2016 at 12:48:21
Specs: Windows 7
I have a list of filters that range from 35-7500 CFM rating. How can I get excel to show which filter is needed by typing in the CFM required?

Example: I want to type in "75" into cell B4. I want cell C4 to tell me what filter fits by using the MAX cfm rating??

message edited by SnoopDoug


See More: How can i find the correct item using a max rating

Report •


#1
February 22, 2016 at 19:19:35
✔ Best Answer
I'm not sure how 75 relates to a MAX CFM rating.

Are you looking to return the closest value to 75 (assuming there is no exact match)? Closest without going under 75? Closest with going over 75?

Maybe a few examples of the desired outputs based on a few inputs might help us help you.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#2
February 23, 2016 at 09:08:01
I am looking to return the value closest to 75 without going over.

I have a list of air filter sorted by their MAX CFM (cubic feet per minute).

The CFM ratings range from 35 - 7500. I would like to type in any number and it give me the filter closest without exceeding the max cfm rating.


Report •

#3
February 23, 2016 at 13:11:58
re: the value closest to 75 without going over

It is still not clear to me if you mean the input value should never be higher than the output value or if the output value should never be higher than the input value Therefore, I'll offer both solutions.

Both formulas will return an exact match if it exists.

Starting with this:

      A         B
1     10	
2     20	
3     30	
4     40	75
5     50	
6     60	
7     70	
8     80	
9     90	
10   100	

For an input of 75, the following formula will return 70.

Output value never greater than input value.

=VLOOKUP(B4,$A$1:$A$10,1,1)

For an input of 75, the following formula will return 80.

Input value never greater than output value.

=INDEX($A$1:$A$10,MATCH(MAX($A$1,B4),$A$1:$A$10)+
ISNA(MATCH(MAX($A$1,B4),$A$1:$A$10,0)))

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
February 24, 2016 at 07:05:19
You are on the right track. I am trying to accomplish your second option.

Input value never greater than output value.

=INDEX($A$1:$A$10,MATCH(MAX($A$1,B4),$A$1:$A$10)+
ISNA(MATCH(MAX($A$1,B4),$A$1:$A$10,0


My input is in B4 and my output is B7. My list is sorted in ascending order in column R (R1-R23). For some reason the formula you provided is still returning an error.

message edited by SnoopDoug


Report •

#5
February 24, 2016 at 07:10:42
I GOT IT!!!! Thank you so so so so much.

Report •


Ask Question