+ Reply to Thread
Results 1 to 8 of 8

How to differentiate exactly word with * and no * on the data to COUNTIF , IFERROR & VLOOK

  1. #1
    Registered User
    Join Date
    07-15-2020
    Location
    united kingdom,England
    MS-Off Ver
    2019
    Posts
    12

    How to differentiate exactly word with * and no * on the data to COUNTIF , IFERROR & VLOOK

    Hi all,

    I need help on this solve this problem

    ~PLEASE Help~The formula on Column B , Column C , Column D cannot differentiate exactly word with * and no * on the data to IFERROR & VLOOK
    =IFERROR(VLOOKUP(A2,Sheet2!$A$2:$C$20,3,FALSE),"")

    The formula on Column E , Column F , Column G working properly with LEN code solve by davsth. Thanks a lot.
    =IFERROR(IF(MATCH(LEFT(A2,LEN(A2)-1)&"~"&RIGHT(A2,1),Sheet2!$A$2:$A$20,0),"O"),"X")

    ~PLEASE Help~The formula on Column H seems not to calculate very well because it calculate all when the data had * on the column A.
    =COUNTIF(Sheet2!$A$2:$A$30,$A$2:$A$30)+
    COUNTIF(Sheet3!$A$2:$A$30,$A$2:$A$30)+
    COUNTIF(Sheet4!$A$2:$A$30,$A$2:$A$30)

    Please somebody help me.

    Thanks
    Attached Images Attached Images
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    07-15-2020
    Location
    united kingdom,England
    MS-Off Ver
    2019
    Posts
    12

    Re: How to differentiate exactly word with * and no * on the data to COUNTIF , IFERROR & V

    For Colum B C D already try new formula but it fail

    =IFERROR(VLOOKUP(IF(MATCH(LEFT(A4,LEN(A4)-1)&"~"&RIGHT(A4,1),Sheet2!$A$2:$A$20,0),3,FALSE),Sheet2!$A$2:$C$20,3,FALSE),"")

  3. #3
    Valued Forum Contributor
    Join Date
    03-24-2020
    Location
    Thailand
    MS-Off Ver
    Office 2016
    Posts
    377

    Re: How to differentiate exactly word with * and no * on the data to COUNTIF , IFERROR & V

    The * is a 'wildcard' symbol. Can you replace it with a number or letter?
    If not, you could use a 'helper' column in all sheets and substitute the "*" with "1" for example, then do the lookup formulas from there. Depending on your actual data, this may not be possible either.
    Otherwise you may need to incorporate a substitute argument into the formulas.
    If your Question is answered; please mark it SOLVED. If you are happy with a member's solution, say 'Thanks' and click the 'Star' to Add Reputation.

  4. #4
    Registered User
    Join Date
    07-15-2020
    Location
    united kingdom,England
    MS-Off Ver
    2019
    Posts
    12

    Re: How to differentiate exactly word with * and no * on the data to COUNTIF , IFERROR & V

    Hi ORoos,

    I still need the data with * at the end because the data exist in the other sheet.

    Now I user formula =IFERROR(VLOOKUP(A2,Sheet2!$A$2:$C$20,3,FALSE),"") on Column A..

    I just need to find the way to combine the formula (IF(MATCH(LEFT(A2,LEN(A2)-1)&"~"&RIGHT(A2,1),Sheet2!$A$2:$A$20,0) into above formula so that I can differentiate the data with *

  5. #5
    Valued Forum Contributor
    Join Date
    12-14-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2007
    Posts
    399

    Re: How to differentiate exactly word with * and no * on the data to COUNTIF , IFERROR & V

    Try
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    07-15-2020
    Location
    united kingdom,England
    MS-Off Ver
    2019
    Posts
    12

    Re: How to differentiate exactly word with * and no * on the data to COUNTIF , IFERROR & V

    Thanks huuthang_bd, your formula is simple and great..

    The other solution was =IFERROR(IF(MATCH(IF(RIGHT(A2)="*",LEFT(A2,LEN(A2)-1)&"~*",A2),Sheet2!$A$2:$A$20,0),VLOOKUP(A2,Sheet2!$A$2:$C$20,3,FALSE),""),"")

    Only one more to solve, column TOTAL MATCH..

    Need to combine =COUNTIF(Sheet2!$A$2:$A$30,$A$2:$A$30)+
    COUNTIF(Sheet3!$A$2:$A$30,$A$2:$A$30)+
    COUNTIF(Sheet4!$A$2:$A$30,$A$2:$A$30)

    with

    =IFERROR(VLOOKUP(SUBSTITUTE(A2,"*","~*"),Sheet2!$A$2:$C$20,3,FALSE),"")

  7. #7
    Valued Forum Contributor
    Join Date
    12-14-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2007
    Posts
    399

    Re: How to differentiate exactly word with * and no * on the data to COUNTIF , IFERROR & V

    As the same
    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    07-15-2020
    Location
    united kingdom,England
    MS-Off Ver
    2019
    Posts
    12

    Re: How to differentiate exactly word with * and no * on the data to COUNTIF , IFERROR & V

    Thank you huuthang_bd

+ 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. [SOLVED] Using IFERROR function with COUNTIF
    By Serpan75 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-15-2020, 07:20 AM
  2. [SOLVED] IFERROR AND COUNTIF function problem
    By akosibhon in forum Excel General
    Replies: 6
    Last Post: 06-19-2014, 03:26 AM
  3. Replies: 1
    Last Post: 01-26-2014, 05:37 PM
  4. [SOLVED] Help please, countif Vlook up???
    By mfhowe1 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-19-2012, 04:32 AM
  5. Round the result of IFERROR / VLOOK function
    By ZJosephine in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-13-2011, 08:15 PM
  6. SUM, IFERROR and COUNTIF
    By Jomathr in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 12-02-2009, 03:14 AM
  7. [SOLVED] How do I use COUNTIF in a SUBTOTAL function to differentiate the .
    By Lettie in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-22-2005, 05:06 AM

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