+ Reply to Thread
Results 1 to 6 of 6

If cell matches any value in Column insert value in adjacent column

  1. #1
    Registered User
    Join Date
    02-24-2012
    Location
    Minnesota
    MS-Off Ver
    ExcelMac2011 Version
    Posts
    3

    If cell matches any value in Column insert value in adjacent column

    -In 1 worksheet I have a list of contacts with adress, city, zip in Columns D, E, F respectively. I'd like to fill column G with the state.
    -In worksheet 2 I have a list of all US zip codes in column A and the corresponding State in Column B.
    -How do I write a formula to reference Column F look for the matching zip in column A of worksheet 2, find the corresponding state in the adjacent column, and insert that state in column G on worksheet 1?

    I imagine this is simple, but am obviously a beginner.

  2. #2
    Forum Expert DGagnon's Avatar
    Join Date
    02-23-2012
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2003, 2007
    Posts
    1,645

    Re: If cell matches any value in Column insert value in adjacent column

    you can do this with a VLOOPUP put this formula in Column G

    =VLOOKUP(F1,Sheet2!A:B,2,0)

  3. #3
    Registered User
    Join Date
    02-24-2012
    Location
    Minnesota
    MS-Off Ver
    ExcelMac2011 Version
    Posts
    3

    Re: If cell matches any value in Column insert value in adjacent column

    Thank you. I used that formula with but had to put in a larger number range lookup. So, =VLOOKUP(F1,Sheet2!A:B,2,55)

    One follow up. I have some zip codes as just 5 digits and the reference in worksheet 2 has all suffixes too. How do I Just reference the 1st 5 digits of the zip codes in column A on worksheet 2?

  4. #4
    Forum Expert DGagnon's Avatar
    Join Date
    02-23-2012
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2003, 2007
    Posts
    1,645

    Re: If cell matches any value in Column insert value in adjacent column

    not sure what you mean by larger number range, the last digit that you modified to 55 would make teh formula error though, could you provide an example?

    also here is the modification to only look at the first 5 digits.

    =VLOOKUP(LEFT(F1,5),Sheet2!A:B,2,0)

  5. #5
    Registered User
    Join Date
    02-24-2012
    Location
    Minnesota
    MS-Off Ver
    ExcelMac2011 Version
    Posts
    3

    Re: If cell matches any value in Column insert value in adjacent column

    See attached. When I used the formula with a 0 as the Range Lookup it errored out. But when I made that number 10000 it populated G correctly.

    I realized the reason many fields appear with #NA is that the referenced column A Sheet 2, begins with a (') before the number. So, I need to identify zip codes in column F sheet 1 that are 4 digits and add a '0 to the beginning of each. For all 5 digit zips I need to add a '

    Of course the actual list is several thousand entries long, so...

    Thanks again.
    Attached Files Attached Files

  6. #6
    Forum Expert DGagnon's Avatar
    Join Date
    02-23-2012
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2003, 2007
    Posts
    1,645

    Re: If cell matches any value in Column insert value in adjacent column

    your list of Zip codes is in a text format, and your data fields are in numaric format, so what you need to do is convert the list to numbers, i did this using =INT(A1) in column C then copying those values back into A

    then i modified the vlookup as follows and it is now working

    =VLOOKUP(INT(LEFT(F2,5)),Sheet2!A:B,2,0)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1