Excel function


Posted by Erin Daly on February 07, 2001 8:04 AM

I'm trying to enter a formula in Excel which does the following........If cell F65 equals 35-44 than show a 5, if cell F65 equals 45-54 than show a 12, if cell F65 equals 55-64 than show a 20, if cell F65 equals any number greater than 65 than show a 30. I've tried numerous combinations but I can't get it to work. Any ideas?! Thanks a lot!

Posted by Scott R on February 07, 2001 8:45 AM

Try this:
=VLOOKUP(F65,{35,5;45,12;55,20;65,30},2)

Posted by Stephen Giles on February 07, 2001 9:33 AM

I am totally confused by the array (is it an array) within the vlookup formula. I have seen this a number of times recently from the "Excel Maffia" - an explanation would be terrific please.

Posted by Bruce on February 07, 2001 9:49 AM

·If VLOOKUP can't find lookup_value, and range_lookup is TRUE, it uses the largest value that is less than or equal to lookup_value.
·If lookup_value is smaller than the smallest value in the first column of table_array, VLOOKUP returns the #N/A error value.

Posted by Scott R on February 07, 2001 9:54 AM

It is a formula containing an array constant. No need to enter using ctrl+shift+enter but you do need to enter the braces. It's just like using VLOOKUP to evaluate a 2 x 4 range of cells except it takes place in one cell. See Excel Help Topic "Values that do not change in array formulas" via Array, Overview for more.

I'm a newcomer to arrays myself thanks to this board; thanks everybody!



Posted by Aladin Akyurek on February 07, 2001 9:57 AM

Yes, it's an array that represents a two-column table which you could ordinarily set up for the problem that is posed. The pair 35, 5; renders the first row of that table, and so on.

Aladin