• Vlookup and Formatting of cells

    Author
    Topic
    #2287284

    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.

    Viewing 9 reply threads
    Author
    Replies
    • #2287294

      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

      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

      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

      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

    • #2287541

      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

      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

      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

      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

      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

      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 9 reply threads
    Reply To: Vlookup and Formatting of cells

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

    Your information: