Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Lounger
    Join Date
    Aug 2012
    Location
    Israel
    Posts
    25
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Can someone explain problems with .xlsx files?

    After a very long period of ignoring .xlsx excel files, I started using them.

    I have problems using vlookup inside those .xlsx files. Is there a way to succeed or maybe they are just not built for vlookup??

    Are there other issues with these files?

    Why would anyone want to use these files at all?

    Thank you.
    Michael

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    There is nothing wrong with xlsx files and you can most certainly use VLOOKUP with them. Can you give a specific example of what doesn't work?
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Lounger
    Join Date
    Aug 2012
    Location
    Israel
    Posts
    25
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by rory View Post
    There is nothing wrong with xlsx files and you can most certainly use VLOOKUP with them. Can you give a specific example of what doesn't work?
    Thank you for your reply.

    Using excel .xls file for vlookup using formula:
    =IF(A9<>"",VLOOKUP(A9,[AAA.xls]Sheet1!$D$5:$G$400,4,FALSE),"")
    This works!

    When testing the .xlsx file it was like this (the formula automatically added the sheet name):
    =IF(A8<>"",VLOOKUP(A8,'f:\DL\[AAA.xlsx]AAA'!$D$5:$G$400,4,FALSE),"")

    This doesn't work!

    Thanks again.
    Michael

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Michael

    ..so you are using Excel2003, but want to link to an external Excel2007(or later) file????

    If you only have Excel2003, then get the newer Excel files saved as the old .xls file format if you want to do that.
    The reason we want to use .xlsx files is that they allow for over 1 million data rows.

    zeddy

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    What does "doesn't work" mean exactly? What happens?

    I note you have changed the sheet name in the formula from Sheet1 to AAA - was that intentional?
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Michael

    Although you can load both .xls and .xlsx files into an Excel2003 session, the external vlookup to the .xlsx file will only work efficiently if that .xlsx file is currently open.

    Your formula includes the pathname to the .xlsx file, so this tells me that the .xlsx file isn't open.
    Perhaps you could open the file, and then check your formulas again. Or maybe the .xlsx file has been moved into a different folder or on a different drive??

    zeddy
    Last edited by zeddy; 2015-05-07 at 04:58.

  7. The Following User Says Thank You to zeddy For This Useful Post:

    Michael Unger (2015-05-07)

  8. #7
    Lounger
    Join Date
    Aug 2012
    Location
    Israel
    Posts
    25
    Thanks
    6
    Thanked 0 Times in 0 Posts
    SeeFormula.jpg
    TheProblem.jpg
    My excel is 2010.
    Last edited by Michael Unger; 2015-05-07 at 05:10.

  9. #8
    Lounger
    Join Date
    Aug 2012
    Location
    Israel
    Posts
    25
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Well, that certainly explains the problem. In this case I don't think it is worth while to have the .xlsx file open all the time.

    Thank you very much for providing this information.

    I'll try to send you a nobel prize by email. You deserve it.
    Michael

  10. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Michael

    The 'F" drive in the formula may indicate that the .xlsx file is on a USB memory stick.
    If it's not plugged in, you would get a #N/A

    To check this, make sure the .xlsx file is currently loaded in your Excel session.

    The other reason you could get a #N/A result is if the lookup value in cell [A8] cannot be found in the specified range.

    zeddy

  11. #10
    Lounger
    Join Date
    Aug 2012
    Location
    Israel
    Posts
    25
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Thank you Zeddy

    Well, the upper line that doesn't work vlookups to xlsx file.
    The second line that DOES work vlookups to xls file.

    The AAA files are identical and both are closed.

    Therefore I think that the problem is that the xlsx file needs TO BE OPEN as Rory mentioned. You also mentioned that the file needs to be LOADED.

    I am a bit disapointed but am very happy that I don't have to have my external files all open all day.

    Michael

  12. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    There shouldn't be a problem in 2010 using VLOOKUP to a closed xlsx file. You say the two files have the same format, which begs the question why you changed the sheet name in the second formula.
    Regards,
    Rory

    Microsoft MVP - Excel

  13. #12
    Lounger
    Join Date
    Aug 2012
    Location
    Israel
    Posts
    25
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by rory View Post
    There shouldn't be a problem in 2010 using VLOOKUP to a closed xlsx file. You say the two files have the same format, which begs the question why you changed the sheet name in the second formula.
    The file AAA is a report from a separate program. I can save it in excel as .xlsx or .xls where .xlsx is default. When I save as .xlsx the sheet name automatically becomes like the file name. I have no clue why that program acts like this.

    I just now started thinking that maybe the right-to-left Hebrew vlookup might be the cause of this whole problem. It could be that vlookup in an .xls file knows it must look right-to-left wheras the .xlsx file is vlookuped left-to-right. I could actually test that by changing the formula a bit.

    Hmm.

    Michael

  14. #13
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Better yet, don't use VLOOKUP at all:

    =IF(A8<>"",INDEX('f:\DL\[AAA.xlsx]AAA'!$G$5:$G$400,MATCH(A8,'f:\DL\[AAA.xlsx]AAA'!$D$5:$D$400,0)),"")
    Regards,
    Rory

    Microsoft MVP - Excel

  15. #14
    Lounger
    Join Date
    Aug 2012
    Location
    Israel
    Posts
    25
    Thanks
    6
    Thanked 0 Times in 0 Posts
    I am having trouble getting the INDEX/MATCH to work.
    I have this formula:
    =INDEX(AAA.xlsx!$G$5:$G$400,MATCH(A8,AAA.xlsx!$D$5 :$D$400,0))
    and receive NotAvailable

    (The AAA.xlsx file is open).
    Michael
    Last edited by Michael Unger; 2015-05-07 at 07:15.

  16. #15
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    You're missing a sheet name in there. (it's easier if you simply select the ranges with the mouse)
    Regards,
    Rory

    Microsoft MVP - Excel

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •