+ Reply to Thread
Results 1 to 14 of 14

Vlookup formulas and cell formatting errors

  1. #1
    Registered User
    Join Date
    01-17-2012
    Location
    Pacific Northwest
    MS-Off Ver
    Excel 2019
    Posts
    87

    Vlookup formulas and cell formatting errors

    [SOLVED] I'm trying to familiarize myself with Excel 10 and I've discovered tiny green triangles in the upper corners of nearly all my numbers data that I've chosen to format as "text". ie. part numbers Since it doesn't seem to happen with ALL numeric data formatted as "text" I can't be certain what exactly causes it but it appears to be a warning of some kind.
    Also I've been experimenting with "Vlookups" and my success there is also hit and miss (even using the same formula). I've attched a small file illustrating my formulas, formatting and the errors I've earned by doing so.
    My hope is that some kind person will have the time to educate me in the error of my ways so that I can avoid these problems in the future.
    I'd very much appreciate it!
    Attached Files Attached Files
    Last edited by imaquila; 02-08-2012 at 01:08 PM.

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: Basic Problems

    VLOOKUP - Take out the Falses at the end and sort the Lookup table in ascending order.
    Also the Price is in column 3 not column 2 (thats Description)

    Your VLOOKUP for Price shoule be this

    =VLOOKUP(D2,$J$3:$L$10,3)

    Note the $ signs

    Format the description column as general, it picks up the descriptions then

    309 appears to be a number. Hit F2 it gets right justified.
    702 appears to be text. Hit F2 its left justified.
    Last edited by Special-K; 01-26-2012 at 07:28 PM.
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Basic Problems

    imaquila,

    Please take a few minutes to read the forum rules, and then amend your thread title accordingly.

    Thanks.
    Entia non sunt multiplicanda sine necessitate

  4. #4
    Registered User
    Join Date
    01-17-2012
    Location
    Pacific Northwest
    MS-Off Ver
    Excel 2019
    Posts
    87

    Question Re: Vlookup formulas and cell formatting errors

    Thanks for your time and effort to help but no luck. The Price was always set to column 3 and most cells were already filled correctly. Description has been my biggest problem and it was always set to column 2 but even adding all the "$"s made no difference. I removed the False part of the Vlookup arguments but still no new cells filled in but two that were working are now returning incorrect values. These are same two cells that you suggested I justify depending upon whether it looked like a number or not (even though I had stated that I formatted the column as text because it is not my intention to do calculations with ItemIDs).

    I got tired of looking at little green triangles that I couldn't correct so I've "ignored" them. Maybe someday someone can enlighten me and I'll correct them the right way.
    I've included a file with "False" arguments removed, and the added "$"signs that you suggested.. If it doesn't help you, maybe someone else will see the source of the problem.

    Thanks again for trying
    Attached Files Attached Files

  5. #5
    Valued Forum Contributor
    Join Date
    08-22-2011
    Location
    Auckland
    MS-Off Ver
    Excel 2019
    Posts
    716

    Re: Vlookup formulas and cell formatting errors

    Hi imaquila.. I changed the cell formatting to general for all except the prices columns. They're still formatted as currency + used Index/Match instead of Vlookup. Vlookup is too finicky. The 309 in J9 was entered with extra blank spaces or something. When the contents were deleted & re entered, it worked.
    Have a look & see if it's what you want
    Please Login or Register  to view this content.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    01-17-2012
    Location
    Pacific Northwest
    MS-Off Ver
    Excel 2019
    Posts
    87

    Re: Vlookup formulas and cell formatting errors

    Thanks for taking the time to look. I had tried using General but (whatever it was I did wrong) it placed everything that "appeared" to be a number and right justified it and then left justified everything else which did not make for easy reading. In the future, I'll try the delete and re-enter approach to see if that will help with my formatting woes. To be honest, VLOOKUP appealed to me because it seemed easy to understand. Index and Match look a bit more intimidating but I'll take some to time to read up on them.
    Thanks again for your time and interest.

  7. #7
    Valued Forum Contributor
    Join Date
    08-22-2011
    Location
    Auckland
    MS-Off Ver
    Excel 2019
    Posts
    716

    Re: Vlookup formulas and cell formatting errors

    Quote Originally Posted by imaquila View Post
    I had tried using General but (whatever it was I did wrong) it placed everything that "appeared" to be a number and right justified it and then left justified everything else which did not make for easy reading.
    If you right click on a cell in column D or J, format cells & look at the alignment tab, you can see that it's all aligned to the left. Otherwise it would display as you said.
    Have fun

  8. #8
    Forum Expert OnErrorGoto0's Avatar
    Join Date
    12-30-2011
    Location
    I DO NOT POST HERE ANYMORE
    MS-Off Ver
    I DO NOT POST HERE ANYMORE
    Posts
    1,655

    Re: Vlookup formulas and cell formatting errors

    Hello,
    You might also use

    =VLOOKUP(IF(ISNA(MATCH(D2,$J$3:$J$10,0)),--D2,D2),J$3:L$10,3,FALSE)

    which checks to see if D2 is located in column J and if so, uses D2 as the look up value, if not it coerces D2 to a true number and uses that.

    Incidentally, I am unsure why you are not using Table references rather than specifying cell ranges by address?
    Good luck.

  9. #9
    Registered User
    Join Date
    01-17-2012
    Location
    Pacific Northwest
    MS-Off Ver
    Excel 2019
    Posts
    87

    Re: Vlookup formulas and cell formatting errors

    That's easy, I've been studying this stuff for about 1 week. Just yesterday I was enlightened on the subject of Name Ranges. I hadn't thought of using Name Ranges for the lookups. Are "$"s assumed as part of a Name Range? Your "--D2,D2" is a mystery to me. Also, is "ISNA" an example of a cell name?

  10. #10
    Registered User
    Join Date
    01-17-2012
    Location
    Pacific Northwest
    MS-Off Ver
    Excel 2019
    Posts
    87

    Re: Vlookup formulas and cell formatting errors

    I'm glad you added that because I went back and tried these types of entries again (numbers that aren't to be used as numbers) and STILL there were those tiny green triangles. Even with using "General" formatting and double checking on extra spaces. However, once I went in and forced the alignment to the left, the triangles were still there but disappeared if I clicked on the troublesome cells. It's nice to finally know how to deal with those now.
    Thanks!

  11. #11
    Forum Expert OnErrorGoto0's Avatar
    Join Date
    12-30-2011
    Location
    I DO NOT POST HERE ANYMORE
    MS-Off Ver
    I DO NOT POST HERE ANYMORE
    Posts
    1,655

    Re: Vlookup formulas and cell formatting errors

    Table references automatically adjust if you add data to the table, so are often better than fixed addresses.

    ISNA() is a function that determines whether an expression evaluates to a #NA error - which is what lookup functions return for no match.

    If D2 is a number stored as text then a mathematical operation will coerce it to a true numeric value. Using -- or *1 or +0 all leave the actual value unaffected.

  12. #12
    Registered User
    Join Date
    01-17-2012
    Location
    Pacific Northwest
    MS-Off Ver
    Excel 2019
    Posts
    87

    Re: Vlookup formulas and cell formatting errors

    Some very useful information! It was just a couple days ago that I posted a question about trying to clear my cells with "#N/A".

    Thank you for sharing!

  13. #13
    Forum Expert OnErrorGoto0's Avatar
    Join Date
    12-30-2011
    Location
    I DO NOT POST HERE ANYMORE
    MS-Off Ver
    I DO NOT POST HERE ANYMORE
    Posts
    1,655

    Re: Vlookup formulas and cell formatting errors

    My pleasure.

    Please see the FAQ link at the top of the page for instructions on how to mark the thread (properly ) Solved.

  14. #14
    Registered User
    Join Date
    01-17-2012
    Location
    Pacific Northwest
    MS-Off Ver
    Excel 2019
    Posts
    87

    Re: Vlookup formulas and cell formatting errors

    What a strange choice for hiding the "Solved" option. Thanks for answering another question I had!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1