Page 1 of 2 1 2 LastLast
Results 1 to 10 of 18

Thread: Vlookup

  1. #1
    Acolyte Jlamarre's Avatar
    Join Date
    Nov 2018
    Posts
    22
    Articles
    0
    Excel Version
    2016

    Vlookup



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello, this might be related to a problem in this thread.

    I read somewhere that a vlookup might be slower then a index/match formula. But for the life of me I can't get it working. (even my vlookup on the information I read looking for does not work). No idea why.

    Now my file is sheet1 (a table) that will gather information from other sheets in the same folder. The other sheets are getting theire information from an Sql query. So in each of the other sheets I have a copy of the sql table. (is this the best setup?)

    Should I use index/match instead of vlookup?

    My file hangs often. That's what I'm trying to resolve.

    Thanks
    Jl


    Sent from my SM-G955W using Tapatalk
    Last edited by Pecoflyer; 2019-04-11 at 07:54 AM. Reason: Removed external link

  2. #2
    Administrator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,673
    Articles
    0
    Excel Version
    2010 on Xubuntu
    External links being potentially unsafe and not available to all our members, please post your sheet on the forum ( no pics please - Thx)
    Thank you Ken for this secure forum.

  3. #3
    Acolyte Heyjoe's Avatar
    Join Date
    Jan 2019
    Location
    USA
    Posts
    55
    Articles
    0
    Excel Version
    2019
    Here is a spreadsheet which teaches you how to do index/match.

    There is an error on the spreadsheet It should say Below are the steps to get to the formula in F2 above, not I2.
    Attached Files Attached Files
    Last edited by Heyjoe; 2019-04-11 at 03:01 PM. Reason: Error in spreadsheet.

  4. #4
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,250
    Articles
    0
    Excel Version
    Office 365 Subscription
    Why not simply correct the spreadsheet before uploading it?
    Ali
    Enthusiastic self-taught user of MS Excel!

  5. #5
    Acolyte Heyjoe's Avatar
    Join Date
    Jan 2019
    Location
    USA
    Posts
    55
    Articles
    0
    Excel Version
    2019
    I found the error after I uploaded it.

  6. #6
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,250
    Articles
    0
    Excel Version
    Office 365 Subscription
    So have you replaced the uploaded spreadsheet with the corrected version? If not, please do so. Thanks.
    Ali
    Enthusiastic self-taught user of MS Excel!

  7. #7
    Acolyte Heyjoe's Avatar
    Join Date
    Jan 2019
    Location
    USA
    Posts
    55
    Articles
    0
    Excel Version
    2019
    Here is a spreadheet that shows you how to write an index match formula.
    Attached Files Attached Files

  8. #8
    Acolyte Jlamarre's Avatar
    Join Date
    Nov 2018
    Posts
    22
    Articles
    0
    Excel Version
    2016
    Quote Originally Posted by Heyjoe View Post
    Here is a spreadheet that shows you how to write an index match formula.
    Thank you guys.

    I did some tests with the file provided. It works! I even did some changes to the file as my file has everything in pivot tables. And it worked. Even changed the table from one sheet to another. Still worked. I was eager to try it out on my file.

    I don't know if it's because I'm in French but no joy. It's not working.

    My problem in the formula is that it can't find my reference in the array. I'm trying to replicate the [#all] after the tables name (in French [#tous]) but with no luck.

    Here is what I tried and it worked (on my cell phone with the Excel that is with my phone). Then tried it on my terminal server (Office 365) with no luck.

    INDEX(Table5[#All],MATCH(Table4[@Salesperson],Table5[[#All],[salesperson]],0),3)

    I'll try again tomorrow. At this time my server just kicked me out for backups. And it's also a good idea to hit the sack. It's past 2300h. 6 more hours to start the last day of the week.

    Thank you again. I'll keep you posted.

    JL

    Sent from my SM-G955W using Tapatalk

  9. #9
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,250
    Articles
    0
    Excel Version
    Office 365 Subscription
    I don't think you need to change #All for a French locale. Try this:

    =INDEX(Table5[#All];EQUIV(Table4[@Salesperson];Table5[[#All];[salesperson]];0);3)

    (courtesy of https://en.excel-translator.de/translator/)
    Ali
    Enthusiastic self-taught user of MS Excel!

  10. #10
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,250
    Articles
    0
    Excel Version
    Office 365 Subscription
    Heyjoe - thanks for uploading the corrected workbook!
    Ali
    Enthusiastic self-taught user of MS Excel!

Page 1 of 2 1 2 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
  •