+ Reply to Thread
Results 1 to 21 of 21

Lookup Problem With Long Argument

  1. #1
    Forum Contributor
    Join Date
    01-22-2013
    Location
    Pekanbaru, Indonesia
    MS-Off Ver
    Excel 2013 (Inc. Power Query)
    Posts
    1,068

    Lookup Problem With Long Argument

    hi all...

    i have 2 formula how to combine them into single formula :

    INDEX($L$2:$L$31;MATCH(A17;$K$2:$K$31;0)+IF(master!F2>0;0;IF(master!F2=0;2;1)))


    i want this below formula can be inserted in above formula
    =VLOOKUP(I2;master!$A$2:$O$5;6;FALSE)

    i have trying to combine it but not working :
    =VLOOKUP(I2;master!$A$2:$O$5;6;FALSE);INDEX($L$2:$L$31;MATCH(A17;$K$2:$K$31;0)+IF(master!F2>0;0;IF(master!F2=0;2;1)))

    my target in cell D17 down and with number lookuo in cell I2, my data source/reference in sheet "master"

    this attachment file

    any help, greatly appreciated..
    Attached Files Attached Files

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,749

    Re: Lookup Problem With Long Argument

    I thought that formula looked familiar

    What would be the requirement for choosing the vlookup part, instead of the INDEX part?

    Normally with something like this, it is a case of...
    =if(test1=TRUE, do vlookup, else do index)
    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

  3. #3
    Forum Contributor
    Join Date
    01-22-2013
    Location
    Pekanbaru, Indonesia
    MS-Off Ver
    Excel 2013 (Inc. Power Query)
    Posts
    1,068

    Re: Lookup Problem With Long Argument

    hi Ford..

    i have trying :

    =IF(I2;TRUE;VLOOKUP(I2;master!A2:E5;4;INDEX($L$2:$L$31;MATCH(A17;$K$2:$K$31;0)+IF(Hitung1!D23>0;0;IF(Hitung1!D23=0;2;1)))))

    still not work....would you help me?

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,749

    Re: Lookup Problem With Long Argument

    Sorry, my example was to show what the syntax would look like, not how the actual formula would look

    You have 2 formulas to choose from (vlookup and INDEX). What would the rule be to decide which 1 to use?

  5. #5
    Forum Contributor
    Join Date
    01-22-2013
    Location
    Pekanbaru, Indonesia
    MS-Off Ver
    Excel 2013 (Inc. Power Query)
    Posts
    1,068

    Re: Lookup Problem With Long Argument

    hi Ford,...i choose to use vlookup

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,749

    Re: Lookup Problem With Long Argument

    No, you misunderstand. If I am correct, you want to combine the vlookup and index into 1 formula, correct?
    i have trying to combine it but not working :
    =VLOOKUP(I2;master!$A$2:$O$5;6;FALSE);INDEX($L$2:$L$31;MATCH(A17;$K$2:$K$31;0)+IF(master!F2>0;0;IF(master!F2=0;2;1)))
    So based on that, you want the formula to make a decision on which 1 to use.

    How would I know that you want "0" (from the vlookup) or "diberikan penyesuaian min/negatif (-) karena lokasinya lebih baik dibandingkan objek penilaian" from the index?

  7. #7
    Forum Contributor
    Join Date
    01-22-2013
    Location
    Pekanbaru, Indonesia
    MS-Off Ver
    Excel 2013 (Inc. Power Query)
    Posts
    1,068

    Re: Lookup Problem With Long Argument

    yes, you're correct i want into 1 formula...

    before it, you have help me, in this post
    https://www.excelforum.com/excel-for...ia-number.html

    i have modify and how to joint 2 formula ito 1 formula

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,749

    Re: Lookup Problem With Long Argument

    Yes I understand you want 1 formula.

    What I am trying to get you to explain to me is how do I decide which 1 to use?

    Under what conditions would I use the vlookup, and when would I use the index?
    What would the rule be?

    for example, if it is Wednesday, I use the vlookup
    if it is not Wednesday, I use index.

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    46,883

    Re: Lookup Problem With Long Argument

    Is it to do with the value in cell I2? If it's 1 use INDEX, if it's 2 use VLOOKUP? Is that it, Jhon?
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  10. #10
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365 ProPlus
    Posts
    9,570

    Re: Lookup Problem With Long Argument

    Are you trying to concatenate the output returned by VLookup and Index formula?

    If so, please try this...

    Please Login or Register  to view this content.
    If that's not what you are trying to achieve, please upload a sample file and mock up the desired output manually in a cell next to the formula cell to show what exactly you are trying to achieve.
    Last edited by sktneer; 08-02-2019 at 02:24 AM.
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  11. #11
    Forum Contributor
    Join Date
    01-22-2013
    Location
    Pekanbaru, Indonesia
    MS-Off Ver
    Excel 2013 (Inc. Power Query)
    Posts
    1,068

    Re: Lookup Problem With Long Argument

    hi sktneer....

    this my new sample...
    my target in D17 down ...yellow & blue shading to easy looked.
    my data in sheet "master" in blue shading..
    lookup number as parameter in cell I2...
    when i type or change number in cell I2, the data display change too depend data in sheet "master"

    sorry my english is not fluent, it's my problem..
    Attached Files Attached Files

  12. #12
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365 ProPlus
    Posts
    9,570

    Re: Lookup Problem With Long Argument

    Sorry it is still not clear.

    e.g. in the sample file you only placed your Index formula in D17. How this formula depends upon what you enter in I2 and how your VLookup formula fits here?

    Also, in the following part of your index formula, you have used relative cell references which will change if you drag down the formula in D17. What's the logic behind this?
    IF(master!F2>0,0,IF(master!F2=0,2,1))

    So you are saying that the formula output in D17 should change if you change the value in I2 but the question remains the same i.e. how?

    I am attaching your file again for you to enter your expected formula output in below cells...

    G17 --> Enter your desired output if the value in cell I2 is 1.
    H17 --> Enter your desired output if the value in cell I2 is 2.
    I17 --> Enter your desired output if the value in cell I2 is 3.

    While manually populating the desired output in G17:I17, explain the logic behind your desired output to show why and how the desired output depends on value in I2.


    After populating the cells G17:I17 with your desired outputs that depend on what you enter in I2, it will help us to visualize the difference between the output returned by the existing Index formula in D17 which is not dependent upon the value in I2 and your desired outputs.

    Also, language shouldn't be a barrier here. If you are not able to explain it properly, you can show us your desired output for different scenarios and to do that you can duplicate the sheet "kertas kerja" few times in the sample workbook, change the value in I2 on each duplicate sheet and manually show us the desired output in column G for the changed value in I2 and explain the logic as much as possible.
    Attached Files Attached Files

  13. #13
    Forum Contributor
    Join Date
    01-22-2013
    Location
    Pekanbaru, Indonesia
    MS-Off Ver
    Excel 2013 (Inc. Power Query)
    Posts
    1,068

    Re: Lookup Problem With Long Argument

    Hi.

    In g17---- result K
    H17------- result J
    I17------reault L
    Last edited by Jhon Mustofa; 08-02-2019 at 08:43 AM.

  14. #14
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365 ProPlus
    Posts
    9,570

    Re: Lookup Problem With Long Argument

    Okay, this is my last attempt to ask you about the logic behind your desired output.

    Forget about the formula and let me know what steps you would manually take to know the desired output considering there is 2 in I2.

    I just want your steps in layman terms...

    e.g.

    Assume that I2=2 then...

    To get the desired output manually, you will first look at the value in A17 which is "Lokasi", then what?
    What would be your next steps to get to the desired output and what do you look in Master sheet and what role has value in I2 (say which is 2) in finding the desired output?

  15. #15
    Forum Contributor
    Join Date
    01-22-2013
    Location
    Pekanbaru, Indonesia
    MS-Off Ver
    Excel 2013 (Inc. Power Query)
    Posts
    1,068

    Re: Lookup Problem With Long Argument

    okay sktneer...

    i try to explain it :

    before, i have a formula that contains :
    Instead number (positive, negative, nol) to be text as reference please see in cell M2 down in sheet "kertas kerja". 3 criteria number always sequence from top to down and always repetition..

    my data in sheet "master" and to convert number to be text (based on data number in sheet "master" see in F2 down right in sheet "master") so i use formula =INDEX($L$2:$L$31;MATCH(A17;$K$2:$K$31;0)+IF(master!F2>0;0;IF(master!F2=0;2;1))) to instead number it

    then i want to use lookup formula to combine or into in a 1 formula or anything..that data can display based on lookup number in cell I2 in sheet "kertas kerja"

    for easy & detail explained , i have modify my new sample with manually result that i want...when I2=1, I2=2 or I2=3 etc....down.
    Attached Files Attached Files
    Last edited by Jhon Mustofa; 08-02-2019 at 01:34 PM.

  16. #16
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365 ProPlus
    Posts
    9,570

    Re: Lookup Problem With Long Argument

    Please try this and see if this is what you are trying to achieve...

    In D17
    Please Login or Register  to view this content.
    and then copy it down.

  17. #17
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    19,403

    Re: Lookup Problem With Long Argument

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

    Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

    (Note: this requirement is not optional. No help to be offered until the link is provided.)

  18. #18
    Forum Contributor
    Join Date
    01-22-2013
    Location
    Pekanbaru, Indonesia
    MS-Off Ver
    Excel 2013 (Inc. Power Query)
    Posts
    1,068

    Re: Lookup Problem With Long Argument

    Quote Originally Posted by sktneer View Post
    Please try this and see if this is what you are trying to achieve...

    In D17
    Please Login or Register  to view this content.
    and then copy it down.
    hi sktneer....thank you so much for your time & effort!!
    working well 100%...

  19. #19
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365 ProPlus
    Posts
    9,570

    Re: Lookup Problem With Long Argument

    You're welcome Jhon!

    Did you pay attention to the administrative post of Alan?

    Posting the same question in other forums is against the forum rule 3 and Alan only asked you to post the question links from other forum here itself but it seems you overlooked that post.

    Anyways, you can still post those links here. I would appreciate if you find some time to do that now.


    All we expect from you to post here like this....

    ****************************************

    I opened the same question here also...

    and post question links here...

    ****************************************

  20. #20
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,749

    Re: Lookup Problem With Long Argument

    sktneer, thanks for the assist on this

  21. #21
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365 ProPlus
    Posts
    9,570

    Re: Lookup Problem With Long Argument

    No problem Ford!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Excel argument is too long. What should I do?
    By gjergji in forum Excel General
    Replies: 4
    Last Post: 07-19-2012, 10:55 AM
  2. [SOLVED] SUMPRODUCT() with LOOKUP() as argument
    By ExcelStefan in forum Excel General
    Replies: 8
    Last Post: 06-08-2012, 10:48 AM
  3. Lookup Func : find w/ argument
    By Kimble in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-15-2008, 06:17 PM
  4. .Range cant take long argument
    By christianlebe in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-11-2008, 12:06 AM
  5. [SOLVED] to long if argument
    By carnelain in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 05-08-2006, 02:10 PM
  6. IF: Argument problem
    By Droman in forum Excel General
    Replies: 1
    Last Post: 01-24-2006, 03:35 AM
  7. UDF argument problem
    By hesselflach@spinnakeradd-ins.com in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-01-2005, 10:05 PM
  8. [SOLVED] Function (array argument, range argument, string argument) vba
    By Witek in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-24-2005, 11:07 AM

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