• Visitors can check out the Forum FAQ by clicking this link. You have to register before you can post: click the REGISTER link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. View our Forum Privacy Policy.
  • Want to receive the latest contracting news and advice straight to your inbox? Sign up to the ContractorUK newsletter here. Every sign up will also be entered into a draw to WIN £100 Amazon vouchers!

Anyone feeling kind?

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

    Anyone feeling kind?

    Someone please help before

    I have added a Vlookup to a spreadsheet but it is consistently throwing up #N/A even though the look-up value is most definitely in the array I have selected. I assumed that there was a formatting error as the data was imported from elsewhere but I have tried Text to Columns, TRIM, CLEAN etc. I have also applied (Len) to check characters in the cell and multiplying cells by one and then copying with paste special and values and also ISNumber() which has given TRUE in all cases. The look-up value is in column A and I have checked and double checked the column number in the table. I have formatted the column showing #N/A and the only thing with that column which makes me think I am missing something there is that a cut and paste formula was recorded in the cell purely as text.

    Any helpful advice gratefully received.
    Connect with me on LinkedIn

    Follow us on Twitter.

    ContractorUK Best Forum Advisor 2015

    #2
    Is Range_Lookup (parameter 4) true or false?

    Comment


      #3
      Hi Brillo

      FALSE and the lookup is numeric
      Connect with me on LinkedIn

      Follow us on Twitter.

      ContractorUK Best Forum Advisor 2015

      Comment


        #4
        Originally posted by LisaContractorUmbrella View Post
        Hi Brillo

        FALSE and the lookup is numeric
        Are the lookup values sorted by numeric value? My guess is that they are not and that will be the problem.
        merely at clientco for the entertainment

        Comment


          #5
          Originally posted by eek View Post
          Are the lookup values sorted by numeric value? My guess is that they are not and that will be the problem.
          Yep they are - in ascending order
          Connect with me on LinkedIn

          Follow us on Twitter.

          ContractorUK Best Forum Advisor 2015

          Comment


            #6
            Originally posted by LisaContractorUmbrella View Post
            Yep they are - in ascending order
            Can you do a copy and paste special (I think the option is as value from memory).
            merely at clientco for the entertainment

            Comment


              #7
              Originally posted by eek View Post
              Can you do a copy and paste special (I think the option is as value from memory).
              Yep tried that one Eek but no luck
              Connect with me on LinkedIn

              Follow us on Twitter.

              ContractorUK Best Forum Advisor 2015

              Comment


                #8
                =VLOOKUP(A2,$A$1:$A$5,1,FALSE)

                Is the range array definition moving due to copying and pasting?
                Never has a man been heard to say on his death bed that he wishes he'd spent more time in the office.

                Comment


                  #9
                  Originally posted by Scrag Meister View Post
                  =VLOOKUP(A2,$A$1:$A$5,1,FALSE)

                  Is the range array definition moving due to copying and pasting?
                  Nope that's all locked in
                  Connect with me on LinkedIn

                  Follow us on Twitter.

                  ContractorUK Best Forum Advisor 2015

                  Comment


                    #10
                    In these situations go to a clean sheet and reimplement something a lot simpler.

                    You've been looking at the sheet now for so long that you won't be able to see the problem.

                    Just go ahead create a new empty sheet and define a simple look up function that should work,

                    As you slowly add functionality to your sheet and build it up again, you should see either the problem not reoccur because now you've defined everything correctly or it does and it becomes obvious.

                    If necessary do a very simple vlookup first one that possibly can't go wrong, and in a completely new sheet and build it up in steps.

                    In a way try to prove the vlookup is broken. Obviously it isn't but assume it is.
                    Last edited by BlasterBates; 5 March 2012, 17:19.
                    I'm alright Jack

                    Comment

                    Working...
                    X