im looking for a script where I can copy and paste a list of names and phones numbers, into excel and have it tell me what state each phone number is from? I have been trying to use vlook myself and have had no success.
In excel of course
Colum A columB
Jeff Blair 5555555555
I was hoping I could find a scrpt that I could just paste these into as they come to me.
Not a script, but a database (mapping area codes to states) and a reference lookup formula. Instead of VLOOKUP, I prefer INDEX combined with MATCH.
First, you need to bring a database mapping the area codes to states into your Excel workbook. Assuming your data is in Sheet1, let’s put the database in Sheet2, starting in cell Sheet2!A1. You can find a current listing of the data at this webpage:
http://www.bennetyee.org/ucsd-pages/area.html
On that page, place your cursor at the start of the table (in front of the"A" in "Area Code in the first field of the table header, depress and hold the left mouse button, and drag the cursor down the page til you get to to final field in the table, just past the last “t” in “present”. Release the left mouse button, click the right mouse button, and select “Copy”. Go back into Excel, and in the worksheet Sheet2, cell A1, select “Paste/Paste Special/Text”. Notice that the first record (row 2) didn’t import cleaning, and just delete row 2. Add underline formatting to row 1 so Excel knows that that row is header information (not necessary, but best practice).
Now go back to Sheet1. Where you have your data in columns A and B, in column C, add this formula:
=INDEX(Sheet2!$A$1:$D$385,MATCH(VALUE(LEFT(B2,3)),Sheet2!$A$1:$A$385,0),2)
Copy it down as necessary. It will lookup the area code in the database and return the matching state.
If you would prefer to use VLOOKUP, you can use:
=VLOOKUP(VALUE(LEFT(B5,3)),Sheet2!$A$1:$D$385,2)
VLOOKUP is best when you need mathematical interpolations in your lookups, as it can return imprecise results. The INDEX function I provided will return an error it can’t find an exact match. To see what I mean, pick an invalid area code, like 222, and see the difference in what the two formulas return.
If you would like more precise geographic information that just state, you can change the last number in either formula from “2” to “4” (which changes the column number in the database from which the result is returned).
Good luck.
VLOOKUP does not return imprecise results, it returns results according to how it is used. So does MATCH. Both functions have an optional argument to specify whether you require an exact match (0 for MATCH, FALSE for VLOOKUP). If you do not specify the optional argument, they both default to finding the largest value that is less than or equal to the lookup value.
The difference is that MATCH returns the position, which then requires INDEX to retrieve the value in that position. VLOOKUP returns the value, and so is the more straightforward solution to doing a value lookup. However, there is no disadvantage to combining INDEX and MATCH to accomplish the same thing other than the formula complexity.
Ok that worked great! and i really dont have a clue how. Awesome response! thanks for the help. I was able to get the vlookup to work but had some minor issues with the match. In the meantime all my numbers are correct so far.. thanks again