Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Page 1 of 3 1 2 3 LastLast
Results 1 to 10 of 26

Thread: Vlookup Cell Containing Symbol & Text

  1. #1
    Join Date
    15th February 2007
    Posts
    103

    Vlookup Cell Containing Symbol & Text

    Hi

    having a problem with a vlookup when the cell value contains ~

    ie. John~Smith

    it returns an #NA even when my lookup table has John~Smith in it.

    The ~ seems to be a problem - is there a work around

    thanks
    Simon

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    18th September 2005
    Location
    Hampshire, UK
    Posts
    1,278

    Re: Vlookup Cell Contains Symbol

    Hi Simon

    In certain operations within Excel the ~ functions as a metacharacter and it is probably this that is causing your lookup to fail. One way that seems to get around this (given a value in A1 and the lookup range in E1:F10 where you are returning F column values) is:

    =INDEX(F1:F10,MATCH(SUBSTITUTE(A1,"~",""),SUBSTITUTE(E1:E10,"~",""),0))

    Confirmed with Ctrl+Shift+Enter. If this has been entered correctly, Excel will surround the resulting formula with curly braces {} - do not enter these manually yourself.

    Hope this helps!

    Richard

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    15th February 2007
    Posts
    103

    Re: Vlookup Cell Contains Symbol

    Hi
    Thanks for the reply.

    When I have large volumes of data those curly bracket {}formulas are always slow.
    I wondered if something could be put round my cell value ie vlookup(B2,list,2,false)

    putting somthing around the B2 part or does it fail whenthe ~ symbol is in my 'list' as well.

    Many thanks

    Simon

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    18th September 2005
    Location
    Hampshire, UK
    Posts
    1,278

    Re: Vlookup Cell Contains Symbol

    The easiest solution would be to get rid of the ~ all together - is there some reason you need to have these in the names?


    EDIT: this is a simpler option but there may be reasons why you don't want to use this:

    =VLOOKUP(SUBSTITUTE(A1,"~","?"),E1:F11,2,0)

    (this isn't an array formula). This will match Simon~Smith to Simon~Smith, but the ? functions as a single character wildcard so it will also match Simon#Smith, SimonDSmith etc - not sure if this cause you problems or not.

    Hope this does help though!

    Richard

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,648

    Re: Vlookup Cell Contains Symbol

    Hi,

    Try,

    =VLOOKUP(SUBSTITUTE(A1,"~","~~"),E1:F10,2,0)

    HTH

  6. #6
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,784

    Re: Vlookup Cell Contains Symbol

    This works for me

    =VLOOKUP("John~Smith",A1:B100,2)

    But will return the last occurence of "John~Smith" if more than 1 exists

  7. #7
    Join Date
    15th February 2007
    Posts
    103

    Re: Vlookup Cell Contains Symbol

    Hi thanks for the replies.

    Dave..=VLOOKUP("John~Smith",A1:B100,2)

    this doesn't work if John~Smith is in cell D1 and you use the formula

    =VLOOKUP(D1,A1:B100,2)

    Thats my problem.

    thanks

    Simon

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,784

  9. #9
    Join Date
    15th February 2007
    Posts
    103

    Re: Vlookup Cell Containing Symbol & Text

    Hi Dave

    Problem is adding false to the formula it will return #NA

    Without false it will return the last record which is not correct

    S
    Last edited by Dave Hawley; June 1st, 2007 at 17:25.

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,784

    Re: Vlookup Cell Containing Symbol & Text

    If you can sort by the names column it will work just fine.

Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Search A Cell For A Symbol
    By shmee150 in forum Excel General
    Replies: 3
    Last Post: August 24th, 2007, 22:22
  2. Replies: 7
    Last Post: March 16th, 2007, 04:55
  3. vlookup with cell instead of a number it's text
    By tiffany18 in forum Excel General
    Replies: 2
    Last Post: July 13th, 2006, 08:36
  4. Vlookup formula appears as text in cell
    By Garth in forum Excel General
    Replies: 3
    Last Post: February 22nd, 2006, 03:23
  5. VLOOKUP to lookup part of the text in a cell
    By cjh1984 in forum Excel General
    Replies: 15
    Last Post: June 3rd, 2004, 00:18

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
  •  
porno