MULTI IN ONE: The LOOKUP Function

<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
The LOOKUP function in Excel is used to search one column of data and find data in the corresponding row. For example, if you are searching a column of employee IDs the LOOKUP function can find, say, employee number 12345 in the ID column. Once it has found the ID 12345 it then can return data from that same row. Let's create a spreadsheet to clear things up.</div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
(The LOOKUP function we'll examine is called a VECTOR LOOKUP. There's also an ARRAY LOOKUP, but we won't cover that here.)</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<br /></div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
Create the following simple spreadsheet:</div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img height="240" src="http://www.homeandlearn.co.uk/excel2007/images/2010/lookup_spreadsheet.gif" width="740" /></div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
So we have item numbers in the A column, then a list of products in the B column. Prices are in the C column. Our spreadsheet only has 10 items, but it could have hundreds, even thousands of entries. What we'd like to do is to take an item number and see what Shoe Type, or what Price corresponds to that item number. An item number of 229, for example, would return either "Stacked heel" or a price of 70. We can use the LOOKUP function for this.</div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
The LOOKUP function needs three between its round brackets. It needs these:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<b>LOOKUP(value_to_lookup, data_to_search, results_column)</b></div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
The value to lookup would be 229 in our example. This is in the A column. So the data to search is A2 to A10. If we want the results to come from the B column, we need the data from B2 to B10. If we wanted the Price to be returned, the data would be in C2 to C10.</div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
So we can add the following LOOKUP function to return a Shoe Type:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<b>=LOOKUP(229, A2:A10, B2:B10)</b></div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
Or this one, for the Price:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<b>=LOOKUP(229, A2:A10, C2:C10)</b></div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
So click into cell F1 in your spreadsheet. Click inside the formula bar and enter:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<b>=LOOKUP(229, A2:A10, B2:B10)</b></div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
When you've entered the formula, press Enter. You'll immediately see that there is a problem:</div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img height="256" src="http://www.homeandlearn.co.uk/excel2007/images/2010/lookup_error.gif" width="752" /></div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
Cell F1 has #N/A in it, indicating there the value is not available. The reason for this error is that LOOKUP needs the data you're searching to be sorted, otherwise problems like this will occur.</div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
To sort the data, highlight the cells A2 to C10. With the cells highlighted, click the <b>Editing</b> panel on the <b>Home</b>ribbon. From the Editing panel, click <b>Sort & Filte</b>r. Then select <b>Smallest to Largest</b> from the menu:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img height="206" src="http://www.homeandlearn.co.uk/excel2007/images/2010/lookup_sort.gif" width="267" /></div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<br /></div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
When your data is sorted, LOOKUP should produce the correct result:</div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<img height="256" src="http://www.homeandlearn.co.uk/excel2007/images/2010/lookup_result.gif" width="752" /></div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
So the Shoe Type for item number 229 is Stacked heel.</div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
There are some things to be aware of with the LOOKUP function. If the item you're searching for is less than the smallest item in your data then an error will occur. As an example, try entering 100 as the first number in your LOOKUP function:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<b>=LOOKUP(100, A2:A10, B2:B10)</b></div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
The N/A error should appear in cell F1 again.</div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
If LOOKUP can't find your value then it will return the highest value lower than your number. So for us, the highest value lower than 229 is 156.</div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
As well as entering a number like 229 you can change it to a cell reference. So the formula could be this:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<b>=LOOKUP(F1, A2:A10, B2:B10)</b></div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
In cell F1 you'd enter the value you want to search for.</div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
The value you're searching for doesn't have to be a number. You can enter text as well. For example, suppose we wanted to return the item number for Mules. We'd do it like this:</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<b>=LOOKUP("Mules", B2:B10, A2:A10)</b></div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
The value to search for is now text, surrounded by double quotes. For us, the text is in the B column, B2 to B10. The Item Number is in the A column, A2 to A10. Excel will search for the text "Mules" in the B column and return the Item Number from the A column. (Instead of typing Mules in double quotes you can change it to a cell reference, if you prefer.)</div>
<div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
<br /></div>
<div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">
In the next section, we'll take a look at VLOOKUP.</div>
</div>

The LOOKUP Function

The LOOKUP function in Excel is used to search one column of data and find data in the corresponding row. For example, if you are searchin...