+ Reply to Thread
Results 1 to 10 of 10

VLOOKUP decided to give me N/A and doesn't want tell me why

  1. 05-22-2021, 12:02 AM #1
    kamil666
    kamil666 is offline
    Registered User
    Join Date
    10-17-2020
    Location
    Denmark
    MS-Off Ver
    MS365
    Posts
    7

    Thumbs up VLOOKUP decided to give me N/A and doesn't want tell me why

    Hello

    I am working with data and I need to VLOOKUP locations to the datasheet SUMS so I will be able to see where the most desired items are located

    Of course VLOOKUP again doesn't want to do it although I tried to format the numbers as numbers, with formula =text, from right and from left...

    Anyone who has some idea WHY Excel doesn't want to work with me? Thank you in advance... I do not know why this program cannot be little more helpful. Instead of solving problem for project I am only solving Excel problems on day to day basis

    I even cannot upload that .xlsx file here because it says Upload or file failed. I am attaching link to Google Drive... Please help me
    drive.google.com/file/d/1X6D07q3xsX1Vmvwi1jJn964cH0Bjn8Y2/view?usp=sharing
    THANK YOU
    Attached Files Attached Files
    Last edited by kamil666; 05-22-2021 at 01:04 AM.
    Reply With QuoteRegister To Reply
  2. 05-22-2021, 12:06 AM #2
    kamil666
    kamil666 is offline
    Registered User
    Join Date
    10-17-2020
    Location
    Denmark
    MS-Off Ver
    MS365
    Posts
    7

    Re: VLOOKUP decided to give me N/A and doesn't want tell me why

    The heading ''Row Labels'' in ''Sums'' sheet should be named as Item #, as is stated in Locations, so ''Row Labels'' contains the same values as ''Item #''
    Reply With QuoteRegister To Reply
  3. 05-22-2021, 12:16 AM #3
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,780

    Re: VLOOKUP decided to give me N/A and doesn't want tell me why

    I won't follow external links, sorry.

    There are instructions at the top of the page explaining how to attach your sample workbook.

    Is this a Google Sheets query?

    Please update your forum profile: there is no 2020 version, so you must have MS365.
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  4. 05-22-2021, 12:23 AM #4
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,929

    Re: VLOOKUP decided to give me N/A and doesn't want tell me why

    Your file is of little help and your explanation even less so

    you dont explain what you are doing
    you dont show what your formula actually is
    "I tried to format the numbers as numbers, with formula =text, from right and from left"
    what exactly does that mean? If you have text numbers, you cannot format them to actual numbers, you need to physically change them somehow.

    VLOOKUP is 1 of the more basic formulas in excel, but it does have very strict criteria requirements, that can make it's use frustrating when not followed properly
    - if your search criteria is numeric, your search range must also be real numeric - formatting wont change that

    Try uploading the file again, following the directions in the yellow banner at the top of the page.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  5. 05-22-2021, 12:33 AM #5
    kamil666
    kamil666 is offline
    Registered User
    Join Date
    10-17-2020
    Location
    Denmark
    MS-Off Ver
    MS365
    Posts
    7

    Re: VLOOKUP decided to give me N/A and doesn't want tell me why

    Thank you for your fast reply, it is great that somebody will help me

    The problem was size of file, now it's uploaded successfully

    I have list of items with occurrences in unique orders and with size of sales for 11 months, and I need to see where the items are located so I can check if it's slotted good or not so good

    I got pretty weird datasheet with many missing numbers, formating from other programs and it's quite overwhelming to think about data preparation and relevance

    Now, only two columns are important - ItemID and Locations. Some ItemID have 5 characters and some 6, so I tried to standardize them with formula =TEXT(A2,"000000") so all of them will be 6 digits long and VLOOKUP will easily find what I want. It didn't
    I copied the result from =TEXT(A2,"000000") as values to another file and tried again. It didn't work

    Basically, I tried everything what I know to make it working but there is something I am now in knowledge with and that ''something'' is acting for VLOOKUP as restriction. Because I cannot explain why it cannot find what I am looking for, when it is in the table, in 1st column, absolutely same as cell where is my first vlookup input

    With ctrl+c and find, I can easily find the location. So values are right. I think, problem is in formating but how to get rid of that terrible formating? Because it seems like it is impossible to get that data uniform and to work with them properly when even paste as values does no change
    Reply With QuoteRegister To Reply
  6. 05-22-2021, 12:37 AM #6
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,780

    Re: VLOOKUP decided to give me N/A and doesn't want tell me why

    VLOOKUP works from left to right, not right to left. Try this:

    =INDEX(Locations[Location],MATCH([@ItemID],Locations[Item '#],0))
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  7. 05-22-2021, 12:37 AM #7
    kamil666
    kamil666 is offline
    Registered User
    Join Date
    10-17-2020
    Location
    Denmark
    MS-Off Ver
    MS365
    Posts
    7

    Re: VLOOKUP decided to give me N/A and doesn't want tell me why

    Guis it work

    i will get back to you how i manage it
    Reply With QuoteRegister To Reply
  8. 05-22-2021, 12:39 AM #8
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,780

    Re: VLOOKUP decided to give me N/A and doesn't want tell me why

    Assuming I've guessed what you wanted to return, yes, it works - look at the file.

    You might need semi-colons for your Danish locale:

    =INDEX(Locations[Location];MATCH([@ItemID];Locations[Item '#];0))
    Last edited by AliGW; 05-22-2021 at 12:42 AM.
    Reply With QuoteRegister To Reply
  9. 05-22-2021, 12:45 AM #9
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,780

    Re: VLOOKUP decided to give me N/A and doesn't want tell me why

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Thanks for the rep.
    Reply With QuoteRegister To Reply
  10. 05-22-2021, 01:03 AM #10
    kamil666
    kamil666 is offline
    Registered User
    Join Date
    10-17-2020
    Location
    Denmark
    MS-Off Ver
    MS365
    Posts
    7

    Re: VLOOKUP decided to give me N/A and doesn't want tell me why

    In my sent file, in ''Sums'' sheet, I copied my result from =TEXT(A2,''000000'') to the 1st column of table and pasted it as values. Now I have formula: =VLOOKUP(D5,Locations,2,FALSE) and it is acting good - D5 is ItemID in 1st column of table, VLOOKUP table contains the same values in 1st column of table. Basically, ItemID (where I want to look for the same numbers) are in both tables in 1st column)


    Thank you many times for Index Match solution. I would very like to look on the exact properties of that formula but I am maybe 3-4 days off of project schedule so I now can only continue in work but I will for sure learn to use Index Match.

    One more time - Huge thank you. I can now continue and that's great

    Have a beautiful weekend and life

    Kamil
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Using two different formulas which should give the same result, but doesn`t
    By RKP in forum Excel General
    Replies: 2
    Last Post: 10-05-2017, 10:45 AM
  2. How to extrapolate CHANGES only between 2 spreadsheets (Inquire doesn't give me what need
    By juthomson in forum Excel General
    Replies: 4
    Last Post: 04-10-2017, 10:03 AM
  3. [SOLVED] Solver doesn't give absolute minimum
    By Siard in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-03-2014, 09:06 AM
  4. Formula doesn't give same results when surrounded by brackets
    By EMaster in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-17-2013, 12:06 AM
  5. Hovering cursor over columns doesn't give values
    By kesmithjr in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 05-09-2013, 09:27 AM
  6. [SOLVED] Vlook up doesn't give correct result
    By Fabienne88 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 03-15-2013, 10:49 AM
  7. Trimmed mean doesn't give expected result
    By Winton in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-03-2012, 06:31 AM

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1