Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Vlookup Cell Containing Symbol & Text

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

  • #2
    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

    Comment


    • #3
      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

      Comment


      • #4
        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

        Comment


        • #5
          Re: Vlookup Cell Contains Symbol

          Hi,

          Try,

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

          HTH
          Kris

          ExcelFox

          Comment


          • #6
            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

            Comment


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

              Comment


              • #8
                Re: Vlookup Cell Contains Symbol

                Hmm, it does for me.

                Comment


                • #9
                  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, 17:25.

                  Comment


                  • #10
                    Re: Vlookup Cell Containing Symbol & Text

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

                    Comment


                    • #11
                      Re: Vlookup Cell Containing Symbol & Text

                      Hi.

                      However you sort it - that formula will also pickup anything else with a ~ in it's sting unfortunately. so if you had Jane~ at A3 and John~Smith at position A4 it will lookup and match to Jane~.
                      It's wierd but it's the ~ symbol that is causing a problem

                      Comment


                      • #12
                        Re: Vlookup Cell Containing Symbol & Text

                        Hi,

                        Did you try my formula?
                        Kris

                        ExcelFox

                        Comment


                        • #13
                          Re: Vlookup Cell Containing Symbol & Text

                          What's the significance of the ~ ?
                          Templates & Calculators

                          Comment


                          • #14
                            Re: Vlookup Cell Containing Symbol & Text

                            Hi All

                            Krish - your formula substitutes only the ~ in the field I am trying to lookup - it exists in my lookup table so would not be found subsequently.

                            Greg - There is no great significance - simply that our database has allowed users to use any symbols in there username including ~. vlookup does not like this symbol - hence the question.

                            Comment


                            • #15
                              Re: Vlookup Cell Containing Symbol & Text

                              Can you attach a sample workbook.It would be better if you include those which produce errors.
                              Kris

                              ExcelFox

                              Comment

                              Trending

                              Collapse

                              There are no results that meet this criteria.

                              Working...
                              X