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

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

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.

re: the value closest to 75 without going overIt 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 100For 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.

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

I GOT IT!!!! Thank you so so so so much.

Ask Your Question

Weekly Poll

Do you think Monopoly should update its pieces?

Discuss in The Lounge

Poll History