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

1. ## 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

2. ## 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. ## 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.

4. ## 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. ## 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. ## 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. ## 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. ## Re: How to differentiate exactly word with * and no * on the data to COUNTIF , IFERROR & V

Thank you huuthang_bd

##### Users Browsing this Thread

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

#### 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