+ Reply to Thread
Results 1 to 21 of 21

Special VLOOKUP , 3 words search

  1. #1
    Forum Contributor
    Join Date
    01-04-2014
    Location
    East Africa
    MS-Off Ver
    MS OFFICE 2019 PRO
    Posts
    3,593

    Special VLOOKUP , 3 words search

    The file looks as follows
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    01-04-2014
    Location
    East Africa
    MS-Off Ver
    MS OFFICE 2019 PRO
    Posts
    3,593

    Re: Special VLOOKUP , 3 words search

    Criteria

    Formula on c1
    LOOK UP D1 into B , if finds 3 words match , plug code on C

  3. #3
    Forum Contributor
    Join Date
    01-04-2014
    Location
    East Africa
    MS-Off Ver
    MS OFFICE 2019 PRO
    Posts
    3,593

    Re: Special VLOOKUP , 3 words search

    Trying to avoid reading since , they are not exact , but match somehow by 3 consecutive words

  4. #4
    Forum Contributor
    Join Date
    01-04-2014
    Location
    East Africa
    MS-Off Ver
    MS OFFICE 2019 PRO
    Posts
    3,593

    Re: Special VLOOKUP , 3 words search

    If the formula does it correctly it will plug on c1 and c2 the following

    8411061788530
    8411061677018

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: Special VLOOKUP , 3 words search

    VBA again ..

    Please Login or Register  to view this content.

  6. #6
    Forum Contributor
    Join Date
    01-04-2014
    Location
    East Africa
    MS-Off Ver
    MS OFFICE 2019 PRO
    Posts
    3,593

    Re: Special VLOOKUP , 3 words search

    John Topley it works beyond awesome , but it is only looking at the same line

    It is not searching data the entire data , if you reverse the data d1 to d2 and d2 to d1 for testing

  7. #7
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: Special VLOOKUP , 3 words search

    It checks column A for number of rows: is your data the same as your test?

    It works OK on your test file: if I reverse D1 and D2 then C1 and C2 are left blank (correctly) as there is no "3 match". If add the original date into rows 3 and 4 I get the correct results in C3 and C4

  8. #8
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: Special VLOOKUP , 3 words search

    I am going out for an hour or so and will respond when I return if still have problems. post a file ???

  9. #9
    Forum Contributor
    Join Date
    01-04-2014
    Location
    East Africa
    MS-Off Ver
    MS OFFICE 2019 PRO
    Posts
    3,593

    Re: Special VLOOKUP , 3 words search

    Here is the file with more rows , as that code worked for that data

    I have jumbled the data so that it should go find where the code is

  10. #10
    Forum Contributor
    Join Date
    01-04-2014
    Location
    East Africa
    MS-Off Ver
    MS OFFICE 2019 PRO
    Posts
    3,593

    Re: Special VLOOKUP , 3 words search

    The file looks as follows the macro not running
    Attached Files Attached Files

  11. #11
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: Special VLOOKUP , 3 words search

    See attached:

    I misunderstood that it needed to compare each entry against all entries in D.

    Questions: what is volume of data (rows) in your "production" file? And number of entries in column D same as A?
    Attached Files Attached Files
    Last edited by JohnTopley; 12-04-2015 at 12:22 PM.

  12. #12
    Forum Contributor
    Join Date
    01-04-2014
    Location
    East Africa
    MS-Off Ver
    MS OFFICE 2019 PRO
    Posts
    3,593

    Re: Special VLOOKUP , 3 words search

    1000 , rows , reading is long , still not VLOOKING UP as needed

  13. #13
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: Special VLOOKUP , 3 words search

    Post back the file showing expected results: I assume if D matches a B then A corresponding to B row is put in C

  14. #14
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: Special VLOOKUP , 3 words search

    Should the value from A correspond to that of row D?

  15. #15
    Forum Contributor
    Join Date
    01-04-2014
    Location
    East Africa
    MS-Off Ver
    MS OFFICE 2019 PRO
    Posts
    3,593

    Re: Special VLOOKUP , 3 words search

    Attached yellow belongs where and green belongs where
    Attached Files Attached Files

  16. #16
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Special VLOOKUP , 3 words search

    Quote Originally Posted by makinmomb View Post
    Attached yellow belongs where and green belongs where
    They can be matched together easily, but below is confusing me:

    B7="ADIDAS TEAM FORCE EDT 100ml+
    B8="ADIDAS TEAM FORCE ENERGISING SHOWER GEL 400ml"

    and

    D1="ADIDAS TEAM FORCE 100ML#1200"
    D6="ADIDAS - TEAM FORCE ENERGISING SHOWER GEL 400ML#900"

    Which belongs to which?
    Quang PT

  17. #17
    Forum Contributor
    Join Date
    01-04-2014
    Location
    East Africa
    MS-Off Ver
    MS OFFICE 2019 PRO
    Posts
    3,593

    Re: Special VLOOKUP , 3 words search

    look up three words is good enough after second word , like TEAM FORCE ENERGISING

    TEAM FORCE EDT is difficult , it can leave that blank , at least 80% of can be matched , remaining 20 percent I can read

  18. #18
    Forum Contributor
    Join Date
    01-04-2014
    Location
    East Africa
    MS-Off Ver
    MS OFFICE 2019 PRO
    Posts
    3,593

    Re: Special VLOOKUP , 3 words search

    If I am able to match by 80% , I only have to read around 400 rows out of the total 2000 lines

  19. #19
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: Special VLOOKUP , 3 words search

    See attached:

    You have to search to find maximum number of matching words otherwise you get erroneous results as pointed out in previous postings.
    Attached Files Attached Files

  20. #20
    Forum Contributor
    Join Date
    01-04-2014
    Location
    East Africa
    MS-Off Ver
    MS OFFICE 2019 PRO
    Posts
    3,593

    Re: Special VLOOKUP , 3 words search

    Yes it worked , blast , It did the crazy search , I am done case , thank you very much

  21. #21
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    27,999

    Re: Special VLOOKUP , 3 words search

    Thank for the feedback.

    Just one observation: can you be more explicit when defining your requirements to try and avoid me (and others) going down the wrong path!

    Thank you.

+ 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. Replies: 0
    Last Post: 02-18-2015, 10:06 AM
  2. Replies: 15
    Last Post: 10-12-2014, 08:53 AM
  3. [SOLVED] How to vlookup to search for three same key words for bashing
    By clifford_ayala in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 11-08-2013, 01:20 AM
  4. To Call All Value Exclude value consist of special words
    By cyee in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-27-2013, 06:12 AM
  5. [SOLVED] Search box in Excel to search key words in sentences in multiple worksheets
    By fernandoii676 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-05-2012, 01:42 PM
  6. Replies: 2
    Last Post: 10-19-2012, 11:11 AM
  7. Replies: 1
    Last Post: 04-04-2012, 07:15 PM

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