Microsoft Excel 2010 - complete product...

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.

✔ 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

I'm not sure what you mean.

I have a spread sheet with a list of filters sorted by their max CFM ratingAre 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

CFMmeans, 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

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?

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

Must type faster.

:-)

-- Jeff, in Minneapolis

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.

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

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

Ask Your Question

Weekly Poll

Do you trust smart speakers to not spy on you?

Discuss in The Lounge

Poll History