Results 1 to 7 of 7

VLOOKUP with text, numbers, and characters in string

This is a discussion on VLOOKUP with text, numbers, and characters in string within the Excel Questions forums, part of the Question Forums category; I am trying to use VLOOKUP on a string that contains text, numbers, and symbols and keep getting a N/A ...

  1. #1
    Board Regular
    Join Date
    Nov 2010
    Posts
    92

    Default VLOOKUP with text, numbers, and characters in string

    I am trying to use VLOOKUP on a string that contains text, numbers, and symbols and keep getting a N/A error. The look-up value is the mixed string of text, characters, and numbers and the return value from the table is numerical. I've tried formating the look-up value as text and still have had no luck. I would appreciate any advice!

    Thanks

  2. #2
    Board Regular alansidman's Avatar
    Join Date
    Feb 2007
    Location
    42.28188; -87.88432
    Posts
    2,574

    Default Re: VLOOKUP with text, numbers, and characters in string

    Would you post some sample data so that we can see specific examples.
    Alan Sidman
    Win 7--Office XP, 2007, 2010

    Be Kind--Use Code Tags. Highlight your code and click on the # sign at the top of the thread window.


  3. #3
    Board Regular
    Join Date
    Nov 2010
    Posts
    92

    Default Re: VLOOKUP with text, numbers, and characters in string

    Sure. In one workbook I have the look-up array which has billing codes in one column and billing descriptions in another column. The codes are numbered 1, 2, 3, ... 29 and the descriptions are 4-4-5 biweekly deduction, semi-annual 12 deductions, semi-annual 24 deductions, quarterly 12 deductions, biweekly billing and deduction,...

    These are just a few examples. I have named the entire array BILLDED and I have named the description column BillDescriptions.

    In another worksheet I have a cell (say A2) with a data validation list of BillDescriptions. In another cell I have a look-up function: =VLOOKUP(A2, BILLDED, 1, FALSE). Here the products codes are in the first column of the table.

    I am recieving a N/A error and I think it is because of the mixed string of data. I'm using Excel 97.

    I tried this problem out at home on a newer version of Excel and was able to solve to problem by moving the BillDescription column ahead of the code column but I'm not sure if this will work in 97.

  4. #4
    Board Regular alansidman's Avatar
    Join Date
    Feb 2007
    Location
    42.28188; -87.88432
    Posts
    2,574

    Default Re: VLOOKUP with text, numbers, and characters in string

    In your lookup table, is the BillDescription in the leftmost column?
    Alan Sidman
    Win 7--Office XP, 2007, 2010

    Be Kind--Use Code Tags. Highlight your code and click on the # sign at the top of the thread window.


  5. #5
    Board Regular
    Join Date
    Nov 2010
    Posts
    92

    Default Re: VLOOKUP with text, numbers, and characters in string

    No it's in the middle of the table

  6. #6
    Board Regular alansidman's Avatar
    Join Date
    Feb 2007
    Location
    42.28188; -87.88432
    Posts
    2,574

    Default Re: VLOOKUP with text, numbers, and characters in string

    Ok. So that is the issue. Look at this tutorial for syntax.

    http://www.techonthenet.com/excel/formulas/vlookup.php

    If you dont want to change your table layout, then you will have to use Index/match and you will need to look at this video

    http://www.datapigtechnologies.com/f...rixlookup.html
    Alan
    Alan Sidman
    Win 7--Office XP, 2007, 2010

    Be Kind--Use Code Tags. Highlight your code and click on the # sign at the top of the thread window.


  7. #7
    Board Regular
    Join Date
    Nov 2010
    Posts
    92

    Default Re: VLOOKUP with text, numbers, and characters in string

    Thanks!

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com