VLOOKUP seems to be searching for formula, rather than value

Vetsus

New Member
Joined
Feb 2, 2014
Messages
35
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:


Sample1.jpg



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:

Sample2.jpg


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:

Sample3.jpg


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:

Sample4.jpg


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
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

circledchicken

Well-known Member
Joined
Aug 13, 2011
Messages
2,932
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,320
Messages
5,600,938
Members
414,417
Latest member
Nobu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top