Monday, October 11, 2010

Lookups in Excel using VLOOKUP

Okay, well maybe excel functions aren't really programming, or aren't quite in par with more complex languages or programming concepts, but it's still required in everyday life. Recently I had to figure out how to correlate values from one datasheet to another datasheet, using some sort of lookup mechanism.  I ran into the function VLOOKUP, but realized there's not much documentation on ranges with VLOOKUP. SO here's the syntax you'll place in your cell (and copy to the last cell of the same column):


=VLOOKUP(A2:A2333,Lookup1!$A$2:$B$3411,2,TRUE)


The first A2:A2333 is the fields you want to lookup, I just select the entire column, makes it easier to duplicate if you have multiple lookups.  If you want you can do it individually and put in A2, A3, A4, etc. in either respective cell. It could get cumbersome with huge sets of data.  Lookup1 is the other datasheet/table you want to use for looking things up.  The $A$2:$B$3411 is the range of the table from A2-A3411 including B2-B3411, it ends up being a giant block of lookups entailing the entire lookup columns/rows.  The 2 is the index in which the lookup data resides. For example 2 correlates to B in this problem.  The last is whether you want an exact match or not, this can be true or false.  It's important that if you are looking up numbers to get the formatting correct, or else it'll error. Be sure you convert to numbers where required.

No comments:

Post a Comment