# 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

#1
February 22, 2016 at 19:19:35
 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.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)))

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,0My 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 •