+ Reply to Thread
Results 1 to 14 of 14

vlookup left is not giving accurate result for relative text

  1. #1
    Registered User
    Join Date
    08-29-2012
    Location
    Riyadh ksa
    MS-Off Ver
    Microsoft Excel 365
    Posts
    83

    vlookup left is not giving accurate result for relative text

    dear all,
    i have applied the formula in A5 of uploaded example file. it should bring the same as the required text but the accurate result did not came. please have a solution for this. or guide me if there is another formula can help.
    regards
    Attached Files Attached Files

  2. #2
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: vlookup left is not giving accurate result for relative text

    try
    =VLOOKUP(LEFT(B5,34)&"*",E2:G6,3,0)
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: vlookup left is not giving accurate result for relative text

    also,
    the E2:C6 should probably look like this $E$2:$G$6
    and, i think, the reason it doesn't work for the first to is the text is different than your lookup data

    Hope this helps
    A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
    -Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
    -To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
    If you received helpful response, please remember to hit the * of that post

  4. #4
    Registered User
    Join Date
    08-29-2012
    Location
    Riyadh ksa
    MS-Off Ver
    Microsoft Excel 365
    Posts
    83

    Re: vlookup left is not giving accurate result for relative text

    please check the example file because exact text is also available in look up data,
    =VLOOKUP(LEFT(B5,34)&"*",E2:G6,3,0) i have already tried this also but it give the relative result not exact.
    please check it again

  5. #5
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: vlookup left is not giving accurate result for relative text

    column B
    "Johnson Baby Wipes*Skin*Care Fragrance Free 64 pcs"
    doesn't equal Column E
    "Johnson Baby Wipes*Skin*Care Fragrance Free 64 PCS 2+2 FREE"
    so the match is not exact,
    it worked for the 3rd item because the text does match

    See Attachment using Martins solution, and my cell addresing fix as well

    Hope this helps
    Attached Files Attached Files

  6. #6
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: vlookup left is not giving accurate result for relative text

    sorry Imram, I NOW see what you are saying, my bad

  7. #7
    Registered User
    Join Date
    08-29-2012
    Location
    Riyadh ksa
    MS-Off Ver
    Microsoft Excel 365
    Posts
    83

    Re: vlookup left is not giving accurate result for relative text

    dear check the column E5 the is equal to B5 "Johnson Baby Wipes*Skin*Care Fragrance Free 64 pcs"
    and B6 equal to E6 "Scholl*Cracked*Heel*Repair*Cream*25*Ml"

  8. #8
    Registered User
    Join Date
    08-29-2012
    Location
    Riyadh ksa
    MS-Off Ver
    Microsoft Excel 365
    Posts
    83

    Re: vlookup left is not giving accurate result for relative text

    please check it again B5 is equal to E5 and B6 is equal to E6

  9. #9
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: vlookup left is not giving accurate result for relative text

    try getting rid of the left statement in the vlookup.. seems to work

    just use
    =VLOOKUP(B5,$E$2:$G:$6,3,0)

    Also, the gcolumn numbers don't match the d column numbers, so the c figures come out wrong

    Hope this helps
    Last edited by dredwolf; 11-15-2012 at 11:49 AM.

  10. #10
    Registered User
    Join Date
    08-29-2012
    Location
    Riyadh ksa
    MS-Off Ver
    Microsoft Excel 365
    Posts
    83

    Re: vlookup left is not giving accurate result for relative text

    dear dredwolf
    now its ok but here is another issue that is,
    this formula only find the exact match but i need the formula same like(=VLOOKUP(LEFT(B5,34)&"*",E2:G6,3,0)) which also find relative text if exact is not available in data,
    so please if you can give me the general formula which work in both condition like,
    if same exact text is not available then it should find the relative item,
    and if same text is available then it should find the same exact text.
    example is
    if i want to find the ""Johnson Baby Wipes*Skin*Care Fragrance Free 64 pcs"" from data and this is available in data then formula should bring same result. but on other hand if the relative text like ""Johnson Baby Wipes*Skin*Care Fragrance Free 64 PCS 2+2 FREE"" then it should also find this and vice versa.
    please your support.
    regards

  11. #11
    Registered User
    Join Date
    08-29-2012
    Location
    Riyadh ksa
    MS-Off Ver
    Microsoft Excel 365
    Posts
    83

    Re: vlookup left is not giving accurate result for relative text

    dear dredwolf
    now its ok but here is another issue that is,
    this formula only find the exact match but i need the formula same like(=VLOOKUP(LEFT(B5,34)&"*",E2:G6,3,0)) which also find relative text if exact is not available in data,
    so please if you can give me the general formula which work in both condition like,
    if same exact text is not available then it should find the relative item,
    and if same text is available then it should find the same exact text.
    example is
    if i want to find the ""Johnson Baby Wipes*Skin*Care Fragrance Free 64 pcs"" from data and this is available in data then formula should bring same result. but on other hand if the relative text like ""Johnson Baby Wipes*Skin*Care Fragrance Free 64 PCS 2+2 FREE"" then it should also find this and vice versa.
    please your support.
    regards

  12. #12
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: vlookup left is not giving accurate result for relative text

    Imran,

    Try something like

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Hope this helps

  13. #13
    Registered User
    Join Date
    08-29-2012
    Location
    Riyadh ksa
    MS-Off Ver
    Microsoft Excel 365
    Posts
    83

    Re: vlookup left is not giving accurate result for relative text

    thanks dredwolf,
    tomorrow i will go to my office and check this with my required data then i will give you confirmation
    thanks once again.
    imran

  14. #14
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: vlookup left is not giving accurate result for relative text

    you are welcome

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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