One of the most useful tools as a voice engineer is the VLOOKUP function in Excel. It can be a bit confusing, though, the first few times you use it, so I figured I’d do a little how-to.
Lets say you have a list of Phone MAC addresses and extensions. Then, you have another list of AD user ID’s, extensions, and their favorite color (why not?). You want to marry these two lists up to build an import, but the order of each is all jacked up.
Start typing VLOOKUP into C1 and you’ll get the function drop-down:
Click on the function, and you’ll get the tooltip with syntax:
This is the part that throws most first-time vlookupers. The lookup value should be your ‘key’ or the common element between the two lists – in this case the 11-digit extension. The table array is a selection of all columns you want to pull values from. The column index number is the column from your array that you want, numbered starting with ‘1’ from left to right. Range lookup is ‘fuzzy logic’ that will pull back the closest match. You typically want this set to FALSE.
So, if we want to pull the User-ID and stick it into column C – here’s what the formula looks like:
If we wanted to pull their favorite color, it would look like this:
And, of course you can copy the function on down in column C and match all extensions to favorite colors: