1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Why the vlookup formula is not working

Discussion in 'VBA Macros' started by paradise, Sep 14, 2018.

  1. paradise

    paradise Member

    Messages:
    148
    Hi,
    I have used formula in J column of 'result' worksheet but it is not working.
    One more most important thing is that if any row is blank in pt worksheet (i.e A7) then the data just above that blank row (i.e A6) result should create an error or blank in result worksheet (i.e in K23)

    Sample result is stated in K column.I have used formula in J column of 'result worksheet which needed improvement as it is not pulling.

    I have posted at https://www.excelforum.com/excel-general/1245319-vlookup-and-substitute.html#post4973888


    Still the result is not received.

    Attached Files:

  2. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    2,003
    Try,

    In J22, copied down :

    =VLOOKUP(SUBSTITUTE($A22," (","("),pt!$A$4:$E$208,3,0)

    Regards
    Bosco
  3. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    812
    This seems to work
    [J22]=VLOOKUP(TRIM(LEFT(A22,FIND("(",A22)-1))&"*";pt!$A$5:$C$8,3,FALSE)

    => It is looking up a partial matching string with the "*" as wildcard.
    => Finding "(" returns the position where that string ends
    => LEFT takes all from that string until the position. Added -1 to get rid of the "("
    => TRIM removes the trailing spaces only

    EDIT: Bosco's solution is way easier, as usual :)
  4. pecoflyer

    pecoflyer Active Member

    Messages:
    256
    Looks like OP is looking for a VB solution...?
  5. Marc L

    Marc L Excel Ninja

    Messages:
    4,257


    So better is to post in the VBA section …​
  6. paradise

    paradise Member

    Messages:
    148
    Thanks for your prompt response.
    But in J23,the result should be blank.Plz refer my above post important criteria mentioned in 3rd line.
    Last edited: Sep 14, 2018
  7. paradise

    paradise Member

    Messages:
    148
    If not possible by formula,I request forum contributor to transfer this thread in VBA section.
  8. Eloise T

    Eloise T Active Member

    Messages:
    811
    What is "pt!" ?
  9. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    812
    The sheetname... Look at the "!".
  10. Eloise T

    Eloise T Active Member

    Messages:
    811
    I see it now...sorry.
  11. paradise

    paradise Member

    Messages:
    148
    Ok a small changes I have done in above workbook which I have highlighted in pt worksheet.Post#2 formula has been mentioned in J column of 'result' worksheet.In order to achieve the desired result as mentioned in K column of 'result' worksheet.

    The formula =VLOOKUP(SUBSTITUTE($A22," (","("),pt!$A$4:$E$208,3,0)

    should be redefined/modified existing formula or be created any new formula instead such that if any data in pt!$A$4:$E$208 is repeated then,it should not pull data.

    Hope now it would ease to all of you.Can be solved by any method vba or formula.

    Attached Files:

  12. paradise

    paradise Member

    Messages:
    148
    also the formula VLOOKUP(SUBSTITUTE($A22," (","("),pt!$A$4:$E$208,3,0) does not work in A25 & A26 of result worksheet.

    Attached Files:

  13. paradise

    paradise Member

    Messages:
    148
    Also in order to create a blank,1st do match A22 result worksheet with that of pt worksheet of A column,if matched then secondly check whether it is duplicate or not.If found duplicate,then place a condition of blank ,else return the value of corresponding column.All these to be mentioned in a single formula without any helper column.

    I hope this will ease further to all in formulating solution.
  14. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    2,003
    Perhaps........

    In "result" sheet J22, copied down :

    =IF(COUNTIF(pt!$A$4:$A$208,INDEX(pt!$A$4:$A$208,MATCH(SUBSTITUTE($A22," ",""),INDEX(SUBSTITUTE(pt!$A$4:$A$208," ",""),0),0)))=1,INDEX(pt!$C$4:$C$208,MATCH(SUBSTITUTE($A22," ",""),INDEX(SUBSTITUTE(pt!$A$4:$A$208," ",""),0),0)),"")

    Regards
    Bosco

    Attached Files:

  15. paradise

    paradise Member

    Messages:
    148
    Thanx a lot BOSCO.You really did it work.

    Your formula seems to work in above demo workbook
    but
    why the formula =VLOOKUP(SUBSTITUTE($A22," (","("),pt!$A$4:$E$208,3,0)
    is not working and what is missing in it.

Share This Page