Solved I need help with a formula

Microsoft Excel 2010 - complete product...
February 23, 2016 at 07:55:40
Specs: Windows 10
I have a spread sheet with a list of filters sorted by their max CFM rating. I want to type a CFM rating into B4 and have C4 tell me what filter to use.

See More: I need help with a formula

Report •


✔ Best Answer
February 23, 2016 at 10:10:02
OK, I get it.

What is your search string?
Is it just 35 or is it 35CFM or some other combination of Numbers & Text?

I'm not sure the =VLOOKUP() function is what your looking for.

The syntax of the VLOOKUP() is:

=VLOOKUP( lookup_value , table_array , column_index_num , [range_lookup] )

It is the last section Range_Lookup where the problem lies.

The Range_lookup argument is a logical value (TRUE or FALSE only) that indicates whether you want VLOOKUP to find an exact or an approximate match to the Lookup_value.

If TRUE or if this argument is omitted, VLOOKUP will use an approximate match if it cannot find an exact match to the Lookup_value. If an exact match is not found, VLOOKUP returns the next largest value that is less than the Lookup_value.

If FALSE, VLOOKUP will only use an exact match to the Lookup_value. If there are two or more values in the first column of Table_array that match the Lookup_value, the first value found is used. If an exact match is not found, a #N/A error is returned.

All that being said, what you may need is an INDEX() - MATCH() formula.

Use INDEX-MATCH:

=INDEX(Return_value_range, MATCH(Lookup_value, Lookup_value_range, Match_type))

Return_value_range - The range that holds the return values

Lookup_value – The value you want to find in the lookup value array

Lookup_value_range – The range containing lookup values

Match_type – Exact (0), Nearest Greater Than (-1), or Nearest Less Than (1)

If you post a small sample of your data, after reading this HOW-TO which explains the use of the < PRE > tags to keep your data aligned correctly, with Column Letters and Row numbers it would help to give you a more exact answer.

http://www.computing.net/howtos/sho...


Or, if you want to try on your own, here is a very good tutorial on Index/Match:

http://www.contextures.com/xlFuncti...

MIKE

http://www.skeptic.com/



#1
February 23, 2016 at 08:29:36
I'm not sure what you mean.

I have a spread sheet with a list of filters sorted by their max CFM rating

Are you talking about Excel filters?
How are you using the filters?
Are you using them to simply sort your data?

I want to type a CFM rating into B4 and have C4 tell me what filter to use.

Not knowing what CFM means, I'm not sure there is a way to return an Excel filter, unless there is a VBA solution.

Unfortunately, my crystal ball is out for repair this week, and from where I'm sitting
I can not see your spreadsheet, so I have absolutely no idea what it is your trying to
do, unless you explain it in more detail.

Also, you asked another question, and DerbyDad03 answered it with a few of his
own questions.

Is your previous question related to this one?

MIKE

http://www.skeptic.com/


Report •

#2
February 23, 2016 at 08:44:03
No. Filter as in Air Filter. (which makes googling a pain in the A). CFM= Cubic feet per minute (How my filters are organized.

Right now I am using a match and a VLOOKUP but it is not quite working the way I need.

My filters are in order from the lowest CFM rating (35) all the way to the highest (7500)

I would like to type in any number as a CFM and it give me the filter that is best rated.

the formulas I am currently using are populating the cells.... but if I type in 36 it will still give me the filter that MAX's at 35.

Does that make any more sense?


Report •

#3
February 23, 2016 at 08:53:24
My guess is that the filters he refers to are air filters or maybe
water filters, since "CFM" is a widely-used initialism for "cubic
feet per minute", a standard measure of flow. You see it used
in specifications for cooling fans, for example.

SnoopDoug must have a list of filters with various flow ratings.
He seems to want to type a number into one cell, and have the
spreadsheet program output a subset of the list which have
flow capacities that match his input. I would expect that if he
has a decent selection of filters, there will usually be several
which have a capacity equal to or greater than the input value.
He will still have to choose among them.

It has been a while since I used Excel, and I don't know how to
do formulas or conditionals in it.

-- Jeff, in Minneapolis


Report •

Related Solutions

#4
February 23, 2016 at 08:55:18
Must type faster.

:-)

-- Jeff, in Minneapolis


Report •

#5
February 23, 2016 at 08:58:12
Thank You sir. It is extremely hard to verbalize excel issues. But you are correct.

The issue I am having now is that it will give me the Air Filter rated for 35CFM when I type in 36CFM. Until I enter a number higher than the next filter which is 75CFM. I would like it to give me the 75CFM filter when I type in any number exceeding 35 and so on.


Report •

#6
February 23, 2016 at 10:10:02
✔ Best Answer
OK, I get it.

What is your search string?
Is it just 35 or is it 35CFM or some other combination of Numbers & Text?

I'm not sure the =VLOOKUP() function is what your looking for.

The syntax of the VLOOKUP() is:

=VLOOKUP( lookup_value , table_array , column_index_num , [range_lookup] )

It is the last section Range_Lookup where the problem lies.

The Range_lookup argument is a logical value (TRUE or FALSE only) that indicates whether you want VLOOKUP to find an exact or an approximate match to the Lookup_value.

If TRUE or if this argument is omitted, VLOOKUP will use an approximate match if it cannot find an exact match to the Lookup_value. If an exact match is not found, VLOOKUP returns the next largest value that is less than the Lookup_value.

If FALSE, VLOOKUP will only use an exact match to the Lookup_value. If there are two or more values in the first column of Table_array that match the Lookup_value, the first value found is used. If an exact match is not found, a #N/A error is returned.

All that being said, what you may need is an INDEX() - MATCH() formula.

Use INDEX-MATCH:

=INDEX(Return_value_range, MATCH(Lookup_value, Lookup_value_range, Match_type))

Return_value_range - The range that holds the return values

Lookup_value – The value you want to find in the lookup value array

Lookup_value_range – The range containing lookup values

Match_type – Exact (0), Nearest Greater Than (-1), or Nearest Less Than (1)

If you post a small sample of your data, after reading this HOW-TO which explains the use of the < PRE > tags to keep your data aligned correctly, with Column Letters and Row numbers it would help to give you a more exact answer.

http://www.computing.net/howtos/sho...


Or, if you want to try on your own, here is a very good tutorial on Index/Match:

http://www.contextures.com/xlFuncti...

MIKE

http://www.skeptic.com/


Report •

#7
February 23, 2016 at 11:00:25
Thank you for the help. I am going to work on this and I will let you know how it goes. You guys are awesome

Report •

Ask Question