Many spreadsheet users will know that when you need to pick out a value from a list, it’s a good idea to use a lookup formula. There are numerous ways to do this, but which is the best? Each formula or method has its advantages and disadvantages depending on the circumstances.
It’s hard to cover all of these circumstances, so we’ll focus on one specific yet common situation. Let’s say you have a unique list of items, say “fruit”. Each type of fruit has a numerical value associated with it — perhaps the quantity or price of the item. Somewhere else in your spreadsheet you want to enter the fruit item you’re interested in and have a formula return the specific value for this fruit item. Let’s further assume that the items are not sorted in any particular order.
The table below gives you the idea.
In this table, we want to enter the name of the fruit in cell B10, and we want the value in cell C10 to automatically pick out the correct quantity in the source data.
So, now that we’ve set the scene, let’s look at 7 different ways to do this in Excel.
Read the rest of this entry »
Posted by Darren Miller 
