Most people use the VLOOKUP function in Excel regularly.
It's a nice enough function, as it does a look-up of a value on a table, and returns the corresponding value of another column of the same table.
Here's an example:
Image:VLOOKUP: nice enough
The vlookup formula takes 4 parameters:

  • The value to look-up in the first column of the range
  • The range itself
  • The column of the range to return
  • A true/false parameter depending on whether you want to look up an exact match, or a closest match
This last parameter is very important. You usually set it to false (or 0), for an exact match.
As an extra bonus, your table does not have to be sorted on the first column !

Now, I'm a big advocate of using Names in Excel.
Formulas get easier to read, and you usually don't have to bother about absolute or relative references.
So, select column A and call it 'Color'.
Select the range (D3:E8) and call it 'ColorList'.
Now your formula can look like:
=VLOOKUP(Color,ColorList,2,FALSE)
Note that, even though 'Color' refers to a whole column, Excel knows you just need the value from one cell A3 !
The advantage is that if you enter e.g. 'blue' in A4, you can enter exactly the same formula in B4 !!
In one of the upcoming Excel-posts I'll be talking about a much quicker way of doing lookups, using MATCH and INDEX, my favorite Excel functions.

Category:  Microsoft Excel  | TechnoratiTechnorati: ,

Comments (0)