News, tips, advice, support for Windows, Office, PCs & more. Tech help. No bull. We're community supported by donations from our Plus Members, and proud of it
Home icon Home icon Home icon Email icon RSS icon
  • Vlookup and Formatting of cells

    Posted on beethoven Comment on the AskWoody Lounge
    Viewing 10 reply threads
    • Author
      Posts
      • #2287284 Reply
        beethoven
        AskWoody Plus

        I am constantly running into difficulties with vlookup and believe the problem is related to the columns not properly identified as numbers. I am really not sure how to change the format properly so that it “sticks”.  If I look at a cell and use format, it shows the cell correctly formatted as number but vlookup fails and the little triangle in the top left corner appears.

        Is there a recommended way to convert the relevant column into number formatting.

      • #2287294 Reply
        Paul T
        AskWoody MVP

        We need a sample sheet to view to work out what isn’t working.

        Alternatively, post the Vlookup and sample data as text.

        cheers, Paul

      • #2287314 Reply
        anonymous
        Guest

        beethoven wrote:
        running into difficulties with vlookup and believe the problem is related to the columns not properly identified as numbers. I am really not sure how to change the format properly so that it “sticks”. If I look at a cell and use format, it shows the cell correctly formatted as number but vlookup fails

        In my experience, this type of problem is usually quite data-specific. That being said, to “convert the relevant column into number formatting” maybe try performing a math operation on data in column (e.g., add 0, or multiply by 1)…

        More specifically, if problem data is in column A, I might try something like this:
        – Insert new column before column A (so problem data now in column B)
        – Format new column A as number
        – Add formula in new column A (below header row) where A2 “= B2+0” & copy down
        – Maybe then copy new column A data & “paste values” back into new column A (to get rid of formula, so you can then delete column B containing problem data)

        Obviously this is general advice that you’ll have to tailor to fit your specific issue, but it’s the best I can offer without more specific info. Good luck.

        Hope this helps.

      • #2287342 Reply
        Lawrence Patterson
        AskWoody Plus

        Hello, and the quick answer is yes the formats of the cell between the value (the column you’re using for defining the lookup) and the table (1st column in the source table) has to be formatted the same.

        Running vlookups for over a decade and using for a variety of different sources (accounting programs, linux raw data, text exports) I typically do the following:

        1. Download both the vlookup source and the results set as separate sheets in the same workbook, with each converted to a table (if this is a living workbook, versus a one time / sledge hammer approach, recommend you name the tables);
          1. See https://opcug.ca/Articles/2019.html Oct – Dec newsletters on a overview of how to setup tables for a quick overview.
        2. In the result sets, add a column, apply the vlookup and see if you encounter an error or not (sometimes it’s not an issue and the vlookup works);
        3. If the vlookup fails, or fails a majority of the time, then take the affected columns, copy the data (see below) to an appropriate formatted column.
          1. Gotcha, when you add a column, it takes the formatting of the adjoining column, which may not be what you want and you need to reset the column’s formatting;
          2. Once the gotcha is done, copy the original column and paste special – values to the new column, and base your vlookup on the new column.
            1. You may have to do this on both the value and the lookup, as imports and original data and the way Excel imports the data can result in unexpected formatting changes to the data.
          3. For living workbooks, I’ve been using the value formula in to automatically reformat the original data into values (just confirm that the formula works consistently).

        As noted by the first responder to your question, there’s multiple ways of handling vlookup data consistency issue.  Hoping the above serves you well.

        Take care,

         

        IT Manager Geek

      • #2287524 Reply
        beethoven
        AskWoody Plus

        Hello everyone and thank you for your assistance.  I think I am trying some of the suggestions already but probably not in a consistent or correct form. I will study the referenced link from Lawrence later this week when I have more time but as a first step, I attach a file with some sample info.

        The spreadsheet is simplified with most columns removed – just for info in the original the final column currently is CJ and the number of rows is 4365.

        In my sample I am trying have a “marker” in column N if the item as referenced in column B is shown in the table in column O.  I also want to have a “indicator” in Column K in the main sheet if the referenced Item no appears in the array in column A in the table.

        I tried formatting both columns, cutting and pasting but without a full success.  Strangely enough (for me) I do get output but not for the whole rows. Items that are clearly included in the table still show up as #N/A  when they should have value,  e.g. Item No 11253.

        I can see how useful this function would be for me but can’t quite get there.

        Cheers

        Beethoven

        Attachments:
      • #2287541 Reply
        Paul T
        AskWoody MVP

        You have numbers (10018) and text (J12104) in the lookup column, is that correct?

        You could add a helper cell in column X, which removes the text – assuming the number is always 5 digits.
        =VALUE(RIGHT(B22,5))

        Then change the VLOOKUP to point to the helper cell. Don’t forget to put dollar symbols before the row so you can use fill to change all cells.
        =VLOOKUP(X22,$Table.A$23:C$585,3,0)

        cheers, Paul

      • #2287545 Reply
        beethoven
        AskWoody Plus

        Paul,

        in principle the Item no is numeric and has 5 digits but for a limited number of cells that rule is broken and the number is in the format J + 5 digits. As the J numbers are the exception and will not be used much longer, I will play with the Helper cell but think maybe my prime issue at the moment is not using the $ sign. I not sure how this should be applied correctly.  I also note that in formula the system generated a ! but your formula is using a . (dot) – what is the difference and effect? Do I need a $ sign before Table? That always comes up with an error.

      • #2287546 Reply
        Paul T
        AskWoody MVP

        The dollars are in front of the row numbers to stop fill changing them.

        Looking at it again you should just use columns, not rows.
        Put this in K2 and fill down, then see if you still need the helper.
        =VLOOKUP(B2,$Table.A:C,3,0)

        cheers, Paul

      • #2287547 Reply
        Paul T
        AskWoody MVP

        Sorry, LibreOffice shows the references differently.

        In Excel:
        =VLOOKUP(B2,Table!A:C,3,0)

        cheers, Paul

        1 user thanked author for this post.
      • #2287853 Reply
        beethoven
        AskWoody Plus

        Paul,

        thanks a lot – working now fine and did not need to use the helper cell.  Still will read up on the info in this link later this week when I have more time.

        Much appreciate all the help

      • #2287890 Reply
        Paul T
        AskWoody MVP

        Oops! The Helper cell is not right either as the letter/number values are in the array (Table), not the VLOOKUP.
        Must be warm and lazy in my head to match the weather.  🙂

        cheers, Paul

    Viewing 10 reply threads

    Please follow the -Lounge Rules- no personal attacks, no swearing, and politics/religion are relegated to the Rants forum.

    Reply To: Vlookup and Formatting of cells

    You can use BBCodes to format your content.
    Your account can't use Advanced BBCodes, they will be stripped before saving.