Lookup Data In Cell & Return Result From An Array Constant

  • <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2 Final//EN"><html><head><meta http-equiv="Content-Language" content="en-us"><title>Excel Lookups With Array Constants</title><meta name="description" content="Excel Lookups With Array Constants"><meta name="keywords" content="Excel Lookups With Array Constants"><link rel="STYLESHEET" href="http://www.ozgrid.com/css/ozgrid.css" type="text/css"><script type="text/javascript" src="http://www.ozgrid.com/SideNavJS/stmenu.js"></script>
    </head><body><a name="top"></a><h1>Excel Lookups With Array Constants</h1><h2>Lookups With Array Constants</h2><p class="j">Would like to show you what I call: <a href="http://www.ozgrid.com/Excel/default.htm/cell-lookup.htm">In-Cell-Lookups</a>. These are the perfect replacement for multiple nested IF functions.</p><p class="j">Enter 1 in cell A1</p><p class="j">Select A1 and while holding down the Ctrl key and drag down by the <a href="http://www.ozgrid.com/Excel/excel-fill-handle.htm">Fill Handle</a> until you reach Cell A20.</p><p class="j">Now, in B1 add this formula;<br><b>=LOOKUP(A1,{1,6,11,16},{&quot;1-5&quot;,&quot;6-10&quot;,&quot;11-15&quot;,&quot;16-20&quot;})</b><br>and double click the <a href="http://www.ozgrid.com/Excel/excel-fill-handle.htm">Fill Handle</a> so this formula is copied down to B20.<br><br>As you can see, this returns a text result of the numeric scope our numbers fall into.<br><br>Here's the details of how this works. Text quoted from Excel help<br><br><i>SYNTAX:=LOOKUP(lookup_value,lookup_vector,result_vector)<br><br><b>lookup_value:</b> Required. A value that LOOKUP searches for in the first vector. Lookup_value can be a number, text, a logical value, or a name or reference that refers to a value.<br><br>l<b>ookup_vector:</b> Required. A range that contains only one row or one column. The values in lookup_vector can be text, numbers, or logical values.<br><br>Important: The values in lookup_vector must be placed in ascending order: ...,-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP might not return the correct value. Uppercase and lowercase text are equivalent.<br><br><b>result_vector:</b> Required. A range that contains only one row or column. The result_vector argument must be the same size as lookup_vector.</i><br><br>For both lookup_vector and Result_vector we have used what is known as <b><a href="http://office.microsoft.com/en-us/excel-help/more-arrays-introducing-array-constants-in-excel-HA001087291.aspx">Array Constants</a></b><br><br>After reading the link above you should understand &quot;Array Constants&quot;. So, as you can see our &quot;lookup_vector&quot; is placed in ascending order using the lowest value for each numeric scope. Our &quot;result_vector&quot; Array Constants <b>correspond</b> to our &quot;lookup_vector&quot; Array constants.</p></body></html>