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
