I'm creating a look up function for the staff where you enter in the zip code, and it returns a series of answers (the state the zip code is attached to, the shipping code to use for regular shipping, next day air, and IF it is available for that zip code, the codes to use for Saturday shipping).
My problem is the fact that I'm looking up zipcodes. I have the cells, both the one where you enter in the zipcode to look up AND the entire column of the 29,000+ zipcodes formatted as "zipcode". However the cell where you enter in the zipcode.. it shows the "0" in the cell, but Excel thinks it's four numbers.. it's not recognizing the "0" in the function line. This means Vlookup is trying to find a 4 digit number, and not the 5 digit one I am asking it to, thus I get #N/A as a result for any zip code starting with "0". Every zipcode that starts with 1 or higher looks up just fine.
I have it set as FALSE so it will return the #N/A when you enter in a nonshippable zipcode, or one that doesn't exist.
If I change to TEXT, then it will not look up anything past "0" in the list, so all answers higher that 070xx (new jersey) come back as #N/A
I'd really like to have just 1 look up, not two, one for Zipcodes starting in "0" and one for zipcodes starting with "1-9". Does anyone know what I can do here? I've tried combinations, but am I just missing something? I'd rather not go back and just drop the 0 in the lookup column of the zipcodes to make this work.. I spent hours reformatting this whole column to show right int he first place.
I have a spreadsheet and I have to fix zip codes everyday for the map to read them Example if zip code is 66606-5648 map will read the zip code, but if its 66606 it will not read it. I need to macro that It will fix the zip code form 66606 to 66606-5648 the zip code doesn’t have to have the correct last four digits just any last four digit I attach a sample of the file.
I am using a Vlookup to pull through information from a worksheet, but I am wanting to vlookup in column a on 12 different worksheets to then pull back corresponding information.
Example
12 worksheets - January through to December.
On each worksheet the first colum is a unique reference number 00001, 00002, etc depending on when we have ordered the goods and each column after that has amount order etc.
In a completely seperate workbook I want to input the unique reference and it to bring back certain information. I have used Vlook up for this, but this only appears to work when searching one worksheet. Is there anyway I can get it to search the whole workbook and bring back the information?
I did have alook across the search forum and web generally and didnt come up with too much luck only found the following [url]
What i am trying to achieve is the following
I have a list of names which i need to do a Vlookup against
the first part works fine
=VLOOKUP(C2,'[ALL_CFA Sales-IM User Profiles edited 2008_11_09.xls]Sales'!$G$2:$H$516,2,0)
Where i come stuck is that some names will be on another worksheet on the same workbook.
I.e i have done a vlookup against sales profile on the first worksheet, but some names shall be on the next worksheet as their "Job Profiles" will be different
Is there a way to extend the vlookup across multiple worksheets in the same workbook,
No two names will be duplicated across the worksheets (unless by pure chance i.e if there happens to be two Steve Smiths this will be two different people and not one indivdual)
On sheet one, I am trying to match the employees job code for that particular week in cell K2. The vlookup/match is trying to find the particular job code for that employee number for that particular week ending.
Cell A2 (sheet 1, shows their employee #), Cell J2 (sheet 1) has that particular week ending. Cell K2 has this formula in sheet 1: =VLOOKUP(A2,Sheet2!A:C,3,MATCH(J2,Sheet2!$D$1:$D$5,0))
Sheet 2 Column A = employee numbers Column C = job codes (which I need to appear in cell K2 for sheet 1) Column D = has the week endings to match against column J in sheet 1
I want to Vlook a value from a cell in another workbook then before I get the formula result I would like it to concatenate the result with another cell. I have attached an example
In Sheet 1 there's a Column B with data I'd like to pull using a VLOOK-UP formula. However, I need it too look-up not only the exact number match in Sheet 2, but also the cell directly above it and the cell directly below it. In the sample above if I placed the VLOOK-UP formula in Sheet 2 (at for instance the numbers 23455, 23456, 23457), I'd need it to pull data from Sheet 1, Column B (for the 23456).
I have a userform where I have 2 comboboxes. The first combobox shows the the first column (only 1 of each) and the second comboBox shows me the secondary list that correlates to the valuse in the first from column B. Now I have a text box that I am trying to get the value from column C depending on what I have in the first 2 comboboxes. What is the easiest way to do it? This is all in VB since it is a UserForm, and using Vlookup seems to be too many lines if I go that route. Is there a way to use Index and Match in VB where it would be more efficient? I attached just a sample of how the data would be layed out in the Excel sheet.
