Hey, did you know that this is VLookup Week?
This is Crystal.
Its been a fun day!
Bill Jelen, who many of you know as MrExcel, started this tradition.
Bill has videos, books, the MrExcel message board, and more.
Bill is a great teacher and I am honored to be in his ranks.
I will be showing you an example that calculates distance between coordinates.
This is done using VLookup to get Latitude and Longitude.
There's also Concatenation
Errors, of course, and how to fix them,
Relative and Absolute References,
Range Names,
Transposing,
VBA,
Table Styles,
Formatting, and Conditional Formatting.
Here is a worksheet created from exporting a table from the reference database
that is posted on my website, http://www.AccessMVP.com/strive4peace/Reference.htm
This data includes Latutude and Longitude, which came in as text.
You can see that immediately as the values in each cell are aligned on the left.
There are also green triangle error indicators in the upper left corner of many of the cells.
Here is a floating icon where I can drop down choices of what I want to do about the selection
since there is something that Excel perceives as a possible error.
I could make my choice here
and do each cell individually ... but that would take a lot of time.
I click on the first Latitude value
hold the Shift key while I press Right Arrow to extend the selection to the next column, containing Longitude, and then Ctrl-Down Arrow to extend the selection to the end of the data
which is at row 42, 767.
Now I drag the scrollbox on the vertical scrollbar to go back to the floating indicator.
Since I want to do calculations, I select Convert to Number.
I notice that different zip codes for the same city have the same coordinates.
I could be using Lat/Long information from GPS data
which would be more accurate, but I don't have it, and I do have this.
Another sheet in this workbook contains information on airports.
I am going to use a VLookup formula to get the zip code for a city so I can find coordinates of airports.
In this cell, I am going to put an equation
so I start by typing an equal sign.
As I type, intellisense prompts me with choices ... and there is VLookup. I press TAB to choose it.
I see the syntax for VLookup
but I like using the dialog box better.
I click the fx icon to the left of the Formula Bar … the Formula (Function) Wizard.
I am prompted for the Vlookup function arguments.
What is the value I want to lookup?
Since the same City name can be in different places,
I concatenate State and City so I have a unique Lookup_value parameter.
The Table_Array will come from my Zips sheet.
Oh! I don't have a column with State and City together.
I need to create one before this will work.
Well I know I will make a new column A
since the lookup value must be in the leftmost column.
I click on cell A2, just to fill it out.
Since I am going to be creating another column
I will define this area to go to column G
then Shift-Ctrl-Down Arrow to extend the selection to the end of the data.
Hey, I even see the values of my array in the wizard dialog box.
The next argument is column number.
Zip code WILL be in column 2 after I add a column to the left of it. OK
Of course, the lookup table isn't set up right yet,
so currently my formula returns a city instead of a zip code.
I go to the Zips sheet
right-click on the Column Header for A
and choose Insert from the shortcut menu.
The label will be StCity.
In the first row of data, the formula is =D2 & C2. ENTER.
That value looks good so I copy it down by double-clicking on the AutoFill handle in the lower right corner of the cell.
The shape of the mouse will be a crosshair, not a plus, when on top of the AutoFill handle.
I best-fit the column by double-clicking just beyond the right edge of the column header boundary line.
I see my zip code has an error indicator.
I don't want to lose my leading zeros by converting this data to a number
and it won't be used in mathmatical calculations.
Text is the correct way to store this information.
The error needs to be ignored.
I will ignore the error for all my zip codes at once.
Shift-Ctrl-Down Arrow
drag the scrollbox to the top of the vertical scrollbar
and choose Ignore Error from the floating menu.
Not that it is necessary
but I am copying the format of a column label that looks good to the one I just created.
Freeze the top row: View ribbon, Freeze Panes, Freeze Top Row
Now I can scroll down and the top row still shows.
Back to my VLookup formula ...
When I copy the formula
I don't want the table array reference to change.
I click on a reference in the formula that is currently Relative
and press the F4 key to toggle it to Absolute.
This adds dollar signs
which is on the 4 key (on the typewriter keypad)
... This is how you can remember that it is F4 to toggle it :)
My lookup value is a concatenation of State and City
D2 & C2
and the column number I want is 2.
My table array starts in column A
not column B.
I also don't need it to go all the way out to H because Zip is in the second column.
I will change the last column to B.
I press ENTER and there is the Zip Code!
Double-click the AutoFill handle to copy it down
I see some errors. I would rather if they didn't show.
I wrap VLookup in a function called IFERROR
The first argument is my equation
The second argument is what I want to be displayed if my equation generates an error message instead of a value.
I want to show nothing
so I specify a zero-length string by typing nothing between quote marks.
Strings are delimited with quotes.
I double-click the AutoFill handle to copy the new version of my formula down.
Error messages go away.
Copy the column labels for Latutude and Longitude from my Zip sheet
and paste them here.
I copy the equation I just made for Zip Code as a starting point for Latitude.
The Lookup Value needs to change because it is a Relative reference and it got adjusted when I pasted the formula.
State and City is = D2 & C2
I enter the formula. Good, a number.
Once again, I paste an equation to start for Longitude.
The table array needs to go to column G
and the column number is 7.
The Lookup Value needs to be changed to D2 & C2
I select the 2 cells containing my new formulas
and double-click the Autofill handle to copy them down.
Now I filter my records for New York.
Uncheck Select All the values
scroll to see the value I want
and there it is.
I check the box to the left of NY
click OK and my filter is applied.
Now I add a New Worksheet for the Distances table I will be creating.
Right-click on the sheet tab and choose Insert from the shortcut menu
and specify Worksheet.
I double-click the sheet name to change it from Sheet1 to Distances.
Now I grab the Airport codes ... copy and paste
I am also going to paste the Airport codes across the top.
I choose Paste Special, check Transpose, and click OK.
I have code to calculate distances between coordinates in an Access database.
I ran My Code Documenter, which is a free download on my website
on a database I know has that code I am looking for.
I filtered the code lines for the word "latitude" to find my GetDistance function.
I copy the procedure to the clipboard using this handy button.
Now I go back to Excel and press Alt-F11 to go to the Visual Basic Editor (VBE).
From the menu, I choose Insert Module... and paste my GetDistance function.
Debug, Compile … and it looks good.
I should really change the module name too, but right now, I am anxious to test it.
I copy the function name so I don't have to type it again.
The order I will need to specify parameters are Latitude and Longitude for the first coordinate
then Latitude and Longitude for the second coordinate.
Since I am going to put a formula in this cell
I start with an equal sign.
Then I paste the function name by pressing Ctrl-V.
I type an open parenthesis because that always comes next.
The first parameter is Latitude for the first coordinate.
I am starting with the cities listed in row 1.
I want Help, so I click the fx button to be prompted for information.
My lookup value is in cell B1
The table array is on my Airports sheet
which has to start with my lookup value column
so I click on the first airport code.
Shift-Click on the first value for Longitude to entend my selection
then Shift-Ctrl-Down Arrow.
My column number is 1, 2, 3, 4, 5.
I click OK and there is first argument for my GetDistance function.
I copy the VLookup equation by by selecting it and pressing Ctrl-C.
Then I Ctrl-V to paste it for the Longitude argument
change the column number to 6
click at the end of the line
and press Ctrl-V to paste again.
This time my lookup value is a different cell.
I select the lookup value reference in my equation and click the cell I want to use instead.
Now I press the F4 key to change the reference type.
I copy this reference, go to the end, and paste it again.
I also adjust my absolute references for my lookup values to be Mixed instead of Absolute so that when the formula is copied, only part of the reference will change.
I double-click the Autofill handle to copy the formulas down …
My lookup table array needs to be Absolute.
I am going to define a Range Name for my lookup table.
I select it by clicking in the top left cell and holding the SHIFT key while I move around.
I click in the Name box and type what I want to call this range 00:10:31.1,00:10:31.0 then ENTER to complete the definition.
press Ctrl-C to copy the name
I replace the the references to my lookup table with my Range Name.
I select each reference and press Ctrl-V to paste.
Now I use the AutoFill handle top copy my new formula to the cell above
and to the cells below.
I still have a problem with Laguardia Airport.
I go over to the Airports sheet and filter the airport code for LGA so I can see what is going on with the data.
Well, its there. Lets take a look at VLOOKUP
There is a 4th argument for VLOOKUP that is optional.
This controls what kind of a match will be found.
The choices are Approximate and Exact.
I want an Exact match
so I will say False for finding an Approximate one.
I have the same problem with the Latide and Longitude data that feeds this lookup formula.
I made a Range Name for my zip code table
which I will use in my formula, when I edit it to say I want an exact match.
I copy the formulas down and now the numbers look better.
Now I have an error I did not have before with the airport in Binghamton.
Oh, I see the problem, there is no Lat/Long information.
That is because the City shows more than one value, so it is not found in my zip code reference.
I see 3 city values that need to be edited.
On my distances sheet
I edit the formula so that the lookup value is a Mixed reference.
I select all the formulas and use the AutoFill handle to copy them across.
it helps to copy the correct formula down before autofilling across.
Beautiful!
I click the Comma Style icon in the Number group on the Home ribbon
and then Decrease Decimal Places twice to show whole numbers.
Now I best-fit all the columns.
They got a little too skinny.
I right-click in any selected column header
choose Column Width... from the shortcut menu
and set it to 8 for all those selected.
That looks good.
I will start coloring my table by choosing a built-in Table Style.
I also check Banded Columns on the Table Tools Design ribbon.
Looking at the Table Style choices again
I will pick one that shows border lines between each cell.
Better, but still not as easy to read as I would like.
I make the pale orange darker for the first row of data. I like that.
I will use the Format Painter to copy this color to other rows.
If the Format Painter is double-clicked
it stays enabled till it is turned off.
Now I will make the column colors different. 00:12:58.1,00:12:58.0 I will choose a pale aqua color.
For this column
In the first row
I want it to be darker
so I choose More Colors... from the paint bucket icon and drag the Shade Slider down to make it darker.
I will use the format Painter to copy the medium color to all the rows with medium orange.
Now I am clicking and Ctrl-clicking on airpost codes that aren't next to each other in the white rows
and I also fill them with white.
In the aqua column for the white rows,
I make the shade even lighter -- just enough not to be white.
I use the Format Painter again to color the column label and then copy the column colors.
I right-align the column labels since they are over numeric data that is right-aligned.
Now I change the color of this text that Excel created to be white so it doesn't show.
I make column A narrower by dragging its right border to the left in the column heading area.
I want to know which airports are close to each other.
I select all my calculated distances and click on Conditional Formatting,
which allows me to set up Rules for how things will look.
I want distances equal to or less than 50 miles to be emphasized.
On the first option
Highlight Cells Rules
I see other comparisons
but not the one I am looking for.
I click More Rules... at the bottom of the flyout menu.
This takes me to a dialog box where I have more choices.
There it is "less than or equal to" on the bottom.
I choose it and type 50 as the value to compare to.
I click the Format... button.
I love all these format choices Excel gives us.
If the rule is true, I will show the text in dark red ... and bold. OK.
I click away from the table to see how it looks.
My color was not a drastic enough change to notice it right away.
I click the Conditional Formatting icon.
Since I am not in a cell with conditional formatting
Excel doesn't know what I want to see.
I drop the list and choose to show formatting rules for This Worksheet.
There is the Rule I set up, so I click on it to select it.
Now I click the Edit Rule button and change the text color to a brighter red.
Perfect!
Thanks, Bill for inviting me to participate in VLookup Week!
... and thanks all the great videos you post so others can learn ~Crystal