• 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...

  • 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

paradise

Member
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.
 

Attachments

  • Substitute &Vlookup.xlsx
    13.5 KB · Views: 11
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 :)
 
Try,

In J22, copied down :

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

Regards
Bosco

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:
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.
 

Attachments

  • Substitute &Vlookup_1.xlsx
    13.5 KB · Views: 0
also the formula VLOOKUP(SUBSTITUTE($A22," (","("),pt!$A$4:$E$208,3,0) does not work in A25 & A26 of result worksheet.
 

Attachments

  • Substitute &Vlookup_1.xlsx
    14.1 KB · Views: 3
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.
 
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
 

Attachments

  • Substitute &Vlookup_1(1).xlsx
    14.6 KB · Views: 6
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.
 
Back
Top