+ Reply to Thread
Results 1 to 8 of 8

Combine VLOOKUP, LEFT, and ISNA to pull area code, place state, leave out #VALUE's?

  1. 02-27-2013, 11:20 AM #1
    chonchos
    chonchos is offline
    Registered User
    Join Date
    09-08-2011
    Location
    Lake City, Florida
    MS-Off Ver
    Excel 2007
    Posts
    3

    Combine VLOOKUP, LEFT, and ISNA to pull area code, place state, leave out #VALUE's?

    I've been trying to wrap my head around this one for a few days. I have two separate semi-working formulas but when I try to "combine" them I get lost. I've googled but I just haven't seen any answers that work yet.

    Basically I have a Quote Log that tracks Company Name, State, Contact Name, Phone Number, etc. I would like the formula to pull the area code from the phone number cell and automatically place the State in another cell. If the phone number cell is empty, show nothing in the state cell. Seems simple enough, especially with two sort-of-working formulas.

    The two formulas are:

    1) =IF(ISNA(VLOOKUP(D8, CODES!$A$2:$B$366, 2, FALSE))=TRUE, "", VLOOKUP(D8, CODES!$A$2:$B$366, 2, FALSE))
    2) =VLOOKUP(VALUE(LEFT(D9,3)), CODES!$A$2:$B$366, 2, FALSE)

    CODES is the other sheet that has the area code lookup list, obviously.

    I'm new to posting in this forum so I'm not sure if this is enough info or too much. If I need more detail I'll gladly give more.

    I'm going to attach a sample file and hopefully it's self-explanatory.

    QUOTE LOG SAMPLE.xlsm

    Please Excel Guru's... lend me your assistance.
    Reply With QuoteRegister To Reply
  2. 02-27-2013, 11:36 AM #2
    benishiryo
    benishiryo is offline
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Combine VLOOKUP, LEFT, and ISNA to pull area code, place state, leave out #VALUE's?

    hi chonchos, welcome to the forum. so in B8, you want to return blank if D8 is blank or if VLOOKUP for the area code is not found? and area code is always the 1st 3 numbers right? try this in B8 if so:
    =IF(OR(D8="",ISNA(VLOOKUP(LEFT(D8,3)+0,CODES!$A$2:$B$366,2,0))),"",VLOOKUP(LEFT(D8,3)+0,CODES!$A$2:$B$366,2,0))

    in Excel 2007, you could even use:
    =IF(D8="","",IFERROR(VLOOKUP(LEFT(D8,3)+0,CODES!$A$2:$B$366,2,0),""))

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."

    Tips & Tutorials I Compiled | How to Get Quick & Good Answers
    Reply With QuoteRegister To Reply
  3. 02-27-2013, 11:45 AM #3
    SDCh
    SDCh is offline
    Valued Forum Contributor
    Join Date
    02-05-2013
    Location
    Jakarta, Indonesia
    MS-Off Ver
    Excel 2013
    Posts
    571

    Re: Combine VLOOKUP, LEFT, and ISNA to pull area code, place state, leave out #VALUE's?

    try this:
    Please Login or Register  to view this content.
    Click (*) if you received helpful response.

    Regards,
    David
    Reply With QuoteRegister To Reply
  4. 02-27-2013, 11:48 AM #4
    dredwolf
    dredwolf is offline
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Combine VLOOKUP, LEFT, and ISNA to pull area code, place state, leave out #VALUE's?

    I took benishiryo's idea just a bit further and created a dynamic named range as well,to let you expand the list (currently will let you go to row 501)
    and used it in the VLOOKUP instead

    (formula is in the green shaded cells)

    Hope this helps
    Attached Files Attached Files
    A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
    -Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
    -To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
    If you received helpful response, please remember to hit the * of that post
    Reply With QuoteRegister To Reply
  5. 02-27-2013, 12:05 PM #5
    chonchos
    chonchos is offline
    Registered User
    Join Date
    09-08-2011
    Location
    Lake City, Florida
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Combine VLOOKUP, LEFT, and ISNA to pull area code, place state, leave out #VALUE's?

    I opened up the sample file from dredwolf and it works great. Just what I was looking for!

    I knew someone could figure it out around here. I guess I was way off with my two "semi-working-formulas" though.
    Reply With QuoteRegister To Reply
  6. 02-27-2013, 12:09 PM #6
    dredwolf
    dredwolf is offline
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Combine VLOOKUP, LEFT, and ISNA to pull area code, place state, leave out #VALUE's?

    You are welcome
    As i stated in post 4 though, it is basically the same as benishiryo's in post 2, I just went a little crazy while I was developing it
    Reply With QuoteRegister To Reply
  7. 02-27-2013, 12:22 PM #7
    chonchos
    chonchos is offline
    Registered User
    Join Date
    09-08-2011
    Location
    Lake City, Florida
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Combine VLOOKUP, LEFT, and ISNA to pull area code, place state, leave out #VALUE's?

    I gave both of you guys the little "star up" credit.
    Reply With QuoteRegister To Reply
  8. 02-27-2013, 12:27 PM #8
    dredwolf
    dredwolf is offline
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Combine VLOOKUP, LEFT, and ISNA to pull area code, place state, leave out #VALUE's?

    Thank you! Much appreciated !
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1