VLOOKUP seems to be searching for formula, rather than value

[Vetsus]

i am trying to get a cell to pull data from a table but it seems that with all the variables i have going on i can't get the data itself to actually pull, if you see the picture below:

i have a phone number in cell E3 which is copied over from a database we use at work. that's why it has the slashes and dashes, which is how i'd like to input the data. in the next picture:

it pulls the are code http://www.vbforums.com/#and prefix of the number in cells H5 and H6 and then in cell H7 it combines the 2 to make 1 solid 6 digit number.

in the next picture:

there is a table with a list of many "areacode" and "prefix" numbers all combined like the 6 digits from the previous picture.

in this picture:

cell J6 (which is selected so you can see the formula i'm using to pull the data) is supposed to take those 6 digits from the second picture, and look them up in the table which is sampled in the 3rd picture to give me the value in the 3rd column of the table to tell me it should be "MS", but instead for some reason i believe it's actually trying to look for the formula which is in picture 2, cell H7 (formula is "=CONCATENATE(H5,H6)") rather than the value which is pulled with the formula as "765586".


my question is, is there something i'm doing wrong? or is there no way to do this? i did try to upload the workbook itself but it seems i'm not knowledgable enough of this site to do so so if you would like to see the copy of the workbook and know how to post it, please let me know and i will do so, otherwise message me and maybe i can get it to you. any help is greatly appreciated. thanks

 

[circledchicken]

Hi Vetsus,

When you use the CONCATENATE function a text string is returned, rather than a number.
Your lookup values in the NPA_NXX table seem to be numeric, so try this:

=VLOOKUP(--'Premium Data'!H7, NPA_NXX, 3, FALSE)

The double negative (--) converts the concatenated string to a numeric value.

Note that it makes these kind of things easier to distinguish if you leave text and numbers with their default formatting (i.e. left and right aligned respectively), rather than aligning everything on one side or in the center.

Also you can use the methods here for posting your data to the forum more effectively:
http://www.mrexcel.com/forum/board-announcements/515787-forum-posting-guidelines.html#post2545970
http://www.mrexcel.com/forum/2198045-post2.html
Excel Jeanie Html

or post your workbook to SkyDrive for example, as a last resort.