+ Reply to Thread
Results 1 to 15 of 15

Libre Office: Dragging down VLOOKUP function

  1. 08-05-2019, 08:49 AM #1
    mmmmmmmmmmmmmmmmmmmm
    mmmmmmmmmmmmmmmmmmmm is offline
    Registered User
    Join Date
    08-02-2019
    Location
    London, England
    MS-Off Ver
    LibreOffice Version: 6.2.5.2
    Posts
    11

    Libre Office: Dragging down VLOOKUP function

    Dear all,

    I have an excel with several sheets. All the sheets have the same structure but different data inside.
    I have a MIN function that looks for the minimum value for the J3 cell in the different sheets.
    What I want to do is find the name of the cell that contains this minimum value.

    The cell J3 contains the minimum number I am looking for, the K3 contains irrelevant information, and the L3 cell contains the name of the sheet too.

    Up until now i have tried with:
    =VLOOKUP(J3;INDIRECT("'"&$J$39:$J$53&"'!J3:L3");3;0)

    Where J39-J53 is a list containing the names of my sheets.

    This works just fine.
    Now, I try to drag this cell to get the exact same result with the following cells.

    I would like to get a result like:

    =VLOOKUP(J4;INDIRECT("'"&$J$39:$J$53&"'!J4:L4");3;0)

    But all I get is exactly the same formula as the first one.

    I am an Excel noob, and I am stuck with thism, could anyone help me?
    Last edited by AliGW; 08-06-2019 at 03:49 AM.
    Reply With QuoteRegister To Reply
  2. 08-05-2019, 10:32 AM #2
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2505 (Windows 11 Home 24H2 64-bit)
    Posts
    91,311

    Re: Dragging down VLOOKUP function

    YOu have posted in the VBA section - I shall move this to the correct section.
    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. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    NB:     as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 08-05-2019, 12:18 PM #3
    63falcondude
    63falcondude is offline
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Dragging down VLOOKUP function

    J3:L3 are in quotes so they will not change as you drag the formula down.

    Maybe you can do something like this:

    =VLOOKUP(J3;INDIRECT("'"&$J$39:$J$53&"'!J"&ROW(J3)&":L"&ROW(L3));3;0)

    This is untested (as I don't have a sample to test on in front of me).
    Reply With QuoteRegister To Reply
  4. 08-06-2019, 03:46 AM #4
    mmmmmmmmmmmmmmmmmmmm
    mmmmmmmmmmmmmmmmmmmm is offline
    Registered User
    Join Date
    08-02-2019
    Location
    London, England
    MS-Off Ver
    LibreOffice Version: 6.2.5.2
    Posts
    11

    Re: Dragging down VLOOKUP function

    Dear 63falcondude,

    Thank you for your comment.
    I tried the formula you suggested but I still could not drag down the funcion and get the numbers to autoincrement.

    Could it possibly be that I am using LibreOffice version 6.2.5.3 instead of Microsoft Office?

    I am trying to attach a file with similar data of what I am working with for testing purposes, but I cannot do it.
    The attachment engine is not working for me.
    If you send me your email I will forward this file to you.
    Reply With QuoteRegister To Reply
  5. 08-06-2019, 03:48 AM #5
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2505 (Windows 11 Home 24H2 64-bit)
    Posts
    91,311

    Re: Dragging down VLOOKUP function

    Could it possibly be that I am using LibreOffice version 6.2.5.3 instead of Microsoft Office?
    This is an interesting disclosure. You have posted in the Excel Functions & Formulas section (moved from the Excel VBA section) - I shall move this again to Other Platforms where it should have been posted in the first instance.
    Reply With QuoteRegister To Reply
  6. 08-06-2019, 05:06 AM #6
    mmmmmmmmmmmmmmmmmmmm
    mmmmmmmmmmmmmmmmmmmm is offline
    Registered User
    Join Date
    08-02-2019
    Location
    London, England
    MS-Off Ver
    LibreOffice Version: 6.2.5.2
    Posts
    11

    Re: Libre Office: Dragging down VLOOKUP function

    Dear all,

    After a few attempts, I got the thing partially working.

    What I did was, I used 63falcondude formula (THANKS!) for the first row (J3 and so on).
    Then I copied it to the second row and manually changed the desired parameters (I incremented the numbers myself).
    So I also obtained the result that I wanted.
    Then I dragged these two down and I got the result wanted.

    Up until now, good.

    My problem now is: this does not work for other columns.
    If I want to use the same formula with columns O-P-Q instead of J-K-L, it does not work.
    What happens is that even if I change the J-K-L values for O-P-Q ones, it still "searches" on the old ones.

    Could anyone help me with that?

    Thanks a lot.
    Reply With QuoteRegister To Reply
  7. 08-06-2019, 05:08 AM #7
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2505 (Windows 11 Home 24H2 64-bit)
    Posts
    91,311

    Re: Libre Office: Dragging down VLOOKUP function

    What is the formula that you are using successfully on column J?
    Reply With QuoteRegister To Reply
  8. 08-06-2019, 05:11 AM #8
    mmmmmmmmmmmmmmmmmmmm
    mmmmmmmmmmmmmmmmmmmm is offline
    Registered User
    Join Date
    08-02-2019
    Location
    London, England
    MS-Off Ver
    LibreOffice Version: 6.2.5.2
    Posts
    11

    Re: Libre Office: Dragging down VLOOKUP function

    Sorry, maybe I didn't explain myself properly.

    In column L I am applying the formula that 63falcondude suggested, which is:

    =VLOOKUP(J3;INDIRECT("'"&$J$39:$J$53&"'!J"&ROW(J3)&":L"&ROW(L3));3;0)

    and this formula is based on information on cell J3, and J3 and L3 for other sheets.
    Reply With QuoteRegister To Reply
  9. 08-06-2019, 05:12 AM #9
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2505 (Windows 11 Home 24H2 64-bit)
    Posts
    91,311

    Re: Libre Office: Dragging down VLOOKUP function

    OK - show us what you did to change it for columns O-P-Q.
    Reply With QuoteRegister To Reply
  10. 08-06-2019, 05:16 AM #10
    mmmmmmmmmmmmmmmmmmmm
    mmmmmmmmmmmmmmmmmmmm is offline
    Registered User
    Join Date
    08-02-2019
    Location
    London, England
    MS-Off Ver
    LibreOffice Version: 6.2.5.2
    Posts
    11

    Re: Libre Office: Dragging down VLOOKUP function

    =vlookup(o3;indirect("'"&$j$39:$j$53&"'!j"&row(o3)&":l"&row(q3));3;0)
    Reply With QuoteRegister To Reply
  11. 08-06-2019, 05:18 AM #11
    romperstomper
    romperstomper is offline
    Forum Expert romperstomper's Avatar
    Join Date
    08-13-2008
    Location
    England
    MS-Off Ver
    365, varying versions/builds
    Posts
    22,026

    Re: Libre Office: Dragging down VLOOKUP function

    You need to change these bits:

    =vlookup(o3;indirect("'"&$j$39:$j$53&"'!j"&row(o3)&":l"&row(q3));3;0)
    Everyone who confuses correlation and causation ends up dead.
    Reply With QuoteRegister To Reply
  12. 08-06-2019, 05:19 AM #12
    Pete_UK
    Pete_UK is online now
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    25,440

    Re: Libre Office: Dragging down VLOOKUP function

    You have not changed the references to j and l in the formula. Try this:

    =vlookup(o3;indirect("'"&$j$39:$j$53&"'!o"&row(o3)&":q"&row(q3));3;0)

    Hope this helps.

    Pete
    Reply With QuoteRegister To Reply
  13. 08-06-2019, 05:24 AM #13
    mmmmmmmmmmmmmmmmmmmm
    mmmmmmmmmmmmmmmmmmmm is offline
    Registered User
    Join Date
    08-02-2019
    Location
    London, England
    MS-Off Ver
    LibreOffice Version: 6.2.5.2
    Posts
    11

    Re: Libre Office: Dragging down VLOOKUP function

    Thank you so much!!!
    That works now.

    Rookie mistake.
    Reply With QuoteRegister To Reply
  14. 08-06-2019, 05:27 AM #14
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2505 (Windows 11 Home 24H2 64-bit)
    Posts
    91,311

    Re: Libre Office: Dragging down VLOOKUP function

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Reply With QuoteRegister To Reply
  15. 08-06-2019, 05:30 AM #15
    mmmmmmmmmmmmmmmmmmmm
    mmmmmmmmmmmmmmmmmmmm is offline
    Registered User
    Join Date
    08-02-2019
    Location
    London, England
    MS-Off Ver
    LibreOffice Version: 6.2.5.2
    Posts
    11

    Re: Libre Office: Dragging down VLOOKUP function

    Done.
    Thank you all.
    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. [SOLVED] Dragging Vlookup
    By Excelski in forum Excel General
    Replies: 3
    Last Post: 02-03-2018, 08:30 AM
  2. Dragging a vlookup formula
    By matt_c_l in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-02-2017, 05:19 PM
  3. [SOLVED] vLookup N/A problem after dragging down list
    By chrissmith1991 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-07-2014, 10:19 AM
  4. [SOLVED] Dragging Vlookup fromula across and down
    By Cortlyn in forum Excel General
    Replies: 2
    Last Post: 10-19-2012, 09:48 AM
  5. [SOLVED] Dragging vlookup across columns
    By ExcelAteMyHomework in forum Excel General
    Replies: 2
    Last Post: 06-01-2012, 03:25 PM
  6. Dragging VLOOKUP
    By SamuelT in forum Excel General
    Replies: 5
    Last Post: 06-13-2006, 11:28 AM
  7. [SOLVED] Dragging VLOOKUP
    By mohd21uk via OfficeKB.com in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 05-18-2006, 05:55 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