+ Reply to Thread
Results 1 to 14 of 14

Exact match for =if(isnumber(find ???

  1. #1
    Registered User
    Join Date
    02-19-2018
    Location
    London, UK
    MS-Off Ver
    2016
    Posts
    11

    Exact match for =if(isnumber(find ???

    Hi

    I would like to find an exact match of a grape within a product name of different wines. The problem is that there are so many blends that if I use =if(isnumber(search or =if(isnumber(find the blends will not show since it will only fish for the first part of the grape name rather than the full cell name as I want it to. E.g. it will tag all the "Cabernet Malbec" and "Cabernet Merlot" as "Cabernet Sauvignon" anyway.

    What formula should I use?

    Please see the Excel Spreadsheet attached for example and desired outcomes.

    Column A - Original name - this is what I already have given.
    Column B - Grape - this is what I want as an end effect. I filled it now manually to show an example. That's where the formula would be.

    Matching process - if "Original Name" includes the full "Look for" (Column E) within its cell, tag is as "Come back with" (Column F).
    If it's not in the list, tag it as "Other".

    Please note that the "Original name will always have the full grape varietal name, no shortcuts (like "chard") are used there.
    They will also always start with a capital letter.
    Attached Files Attached Files
    Last edited by anabanana253; 02-19-2018 at 11:26 AM. Reason: solved

  2. #2
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    2,108

    Re: Exact match for =if(isnumber(find ???

    Maybe something like this:
    Array formula Ctrl+Shift+Enter

    Please Login or Register  to view this content.
    But without a small Excel sample, we cannot do much.

  3. #3
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: Exact match for =if(isnumber(find ???

    you may need to use a lookup
    for example
    trapiche syrah reserve
    does not have
    Shiraz anywhere in the name, so a search would not work

    how long is the list and possible lookup table

    I dont think you can look for Shiraz on its own , as that would be grouped together

    But please do not post images, otherwise someone may need to create the spreadsheet - which maybe a lot of work, and put some members off
    instead
    Please upload a small clean sample of your data / workbook (not a picture) to the forum, Make sure you have removed any private information, remember this is a public forum and so available to anyone
    We would like to see an example of your data and also a manual mock up of the expected results you want to achieve.

    To attach a file to your post,
    click "Go advanced" (next to quick post),
    scroll down until you see "manage Attachments",
    click that and select "Choose File" (top Left corner).
    Find your file, click "Open" click "upload" click 'close windows" Top Right. click "Submit Reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  4. #4
    Registered User
    Join Date
    02-19-2018
    Location
    London, UK
    MS-Off Ver
    2016
    Posts
    11

    Re: Exact match for =if(isnumber(find ???

    Hi Etaf. Thanks for the tips. I now added an example data file. The original one has almost 25,000 rows and there are 61 combinations of grape varietals but I thought 14 is an ok number to work on as example. Hope this is ok.

  5. #5
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: Exact match for =if(isnumber(find ???

    PaulM100 formula will work
    BUT the order of the table is very important , otherwise you will get the wrong result
    As you can see i have put just the single words at the end, otherwise , they would return the wrong result

    also I have highlighted yellow, where the result is different to your sample - BUT i think the result is correct

    I have also added where the words are interchanged ie

    Malbec Syrah
    Syrah Malbec

    Both need to be in the lookup table

    we can also use an IFERROR ()
    to return other instead of #N/A

    SO the 61 combinations need to be carefully ordered to get the correct results in the desired output

    see what you thing of the attached
    Attached Files Attached Files
    Last edited by etaf; 02-19-2018 at 09:04 AM.

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Exact match for =if(isnumber(find ???

    etaf is correct. Table order is important. To get the correct result, it MUST be ordered in descending word number order. Otherwise it will fail at (for example) row 9. It is also necessary to remove the surplus - from the names to get a correct match. i have assumed that you expected answer at row 28 is incorrect.

    Ordinary formula:
    =IFERROR(INDEX($F$3:$F$18,MATCH(TRUE,INDEX(ISNUMBER(SEARCH($E$3:$E$18,TRIM(SUBSTITUTE(A3,"-","")))),),0)),"Other")
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  7. #7
    Registered User
    Join Date
    02-19-2018
    Location
    London, UK
    MS-Off Ver
    2016
    Posts
    11

    Re: Exact match for =if(isnumber(find ???

    I see, ok. So I need to include all the possible combinations in columns E and F as well as remove signs/interpunction.
    I understand that this will only find "Cabernet Merlot" if these words are next to each other?
    E.g. if I'll have "Punto Cabernet Ibiza Merlot" it won't work?

    Thanks for your help!

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Exact match for =if(isnumber(find ???

    It is also necessary to remove the surplus - from the names to get a correct match.... I removed it in the formula that I used: TRIM(SUBSTITUTE(A3,"-",""))

    It will NOT work with Punto Cabernet Ibiza Merlot. I haven't tried it, but that should probably return Cabernet... Is that "split name" a common/predictable occurrence?

  9. #9
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Exact match for =if(isnumber(find ???

    Can you provide a truly REPRESENTATIVE sample?

  10. #10
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Exact match for =if(isnumber(find ???

    I had a moment of "inspiration".... try this. No need for Shiraz Malbec and Malbec Shiraz as separate entries. also works with split varietals, I think.
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    02-19-2018
    Location
    London, UK
    MS-Off Ver
    2016
    Posts
    11

    Re: Exact match for =if(isnumber(find ???

    Oh wow, that looks good! I will add all the other combinations now and test it :D

  12. #12
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: Exact match for =if(isnumber(find ???

    excellent, been following too.

  13. #13
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Exact match for =if(isnumber(find ???

    I removed a pile of surplus () and put things in a more logical order:

    =IFERROR(INDEX(H:H,1/(1/MAX(ISNUMBER(SEARCH($E$3:$E$16,A3))*ISNUMBER(SEARCH($F$3:$F$16,A3))*ISNUMBER(SEARCH($G$3:$G$16,A3))*ROW($E$3:$E$16)))),"No Match")

    Please note: (I forgot to say)... this is an array formula. Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

    You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

    Don't type the curly brackets yourself - it won't work...

  14. #14
    Registered User
    Join Date
    02-19-2018
    Location
    London, UK
    MS-Off Ver
    2016
    Posts
    11

    Re: Exact match for =if(isnumber(find ???

    Hi Glenn

    I think it worked! Good that you mentioned the CTRL + SHIFT + ENTER becasue I didn't know this.

    Also - it didn't work completely for me because my three columns were not ordered gradually. E.g. I had something like this:
    11111.png

    I realised that all the options with 1 Description must go first:
    22222.png

    I will keep checking the data but I think you solved it!

    Thanks so much for your help

+ 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. add in the parameters to the Find function to only find an exact match.
    By jakeembx in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-07-2014, 08:57 AM
  2. [SOLVED] Problem with Find function. Need to find Exact match
    By SMILE in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-06-2012, 07:07 AM
  3. Find exact match in two collumn. Please help
    By 11ktran44 in forum Excel General
    Replies: 1
    Last Post: 02-23-2012, 04:00 AM
  4. Find exact match
    By indimonk in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-30-2012, 11:25 PM
  5. using Exact with isnumber
    By benaw in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-26-2011, 01:02 AM
  6. [SOLVED] Find exact match.
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-13-2006, 12:55 PM
  7. Find a not exact match using vlookup
    By Russ B in forum Excel General
    Replies: 1
    Last Post: 07-27-2005, 04:05 PM
  8. [SOLVED] using vlookup to find exact match
    By Janice Lee via OfficeKB.com in forum Excel General
    Replies: 4
    Last Post: 03-24-2005, 10:46 PM

Tags for this Thread

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