+ Reply to Thread
Results 1 to 16 of 16

Using match and left function to detect fraud

  1. #1
    Registered User
    Join Date
    11-22-2014
    Location
    texas
    MS-Off Ver
    2010
    Posts
    3

    Question Using match and left function to detect fraud

    Hi I'm doing accounting homework but stuck here.

    SSN
    345-88-9877
    913-57-8024
    024-56-8976

    Assume SSN that begins with digit 9 is invalid. I need to detect the invalid SSNs. If the ssn is invalid then display "invalid", or, leave it blank.
    I was using this formula: =if(isna(match(9,left(a2,1),0))," ","invalid") but excel tell me I'm wrong. Can you help me?

  2. #2
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Using match and left function to detect fraud

    Try

    =if(left(a2,1)="9","invalid","")

    Match() is used to find a value in a range of cells, not a character in a single cell. If you only want to inspect the first character, then Left() is the function you need.

    cheers, teylyn

  3. #3
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Using match and left function to detect fraud

    Try this one

    =IF(COUNTIF(A1,"9"&"*"),"Invalid","Ok")

    Row\Col
    A
    B
    1
    345-88-9877 Ok
    2
    913-57-8024 Invalid
    3
    024-96-8976 Ok
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  4. #4
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Using match and left function to detect fraud

    @AlKey, CountIf() is designed to be used on a range of cells. Although you can limit the range to a single cell, there are better ways to do that. To inspect a single cell, string functions that operate on a single cell like Left(), Right(), Mid(), Find(), Search(), etc are a better fit.

  5. #5
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Using match and left function to detect fraud

    Quote Originally Posted by teylyn View Post
    @AlKey, CountIf() is designed to be used on a range of cells. Although you can limit the range to a single cell, there are better ways to do that. To inspect a single cell, string functions that operate on a single cell like Left(), Right(), Mid(), Find(), Search(), etc are a better fit.
    I would disagree.

  6. #6
    Registered User
    Join Date
    11-22-2014
    Location
    texas
    MS-Off Ver
    2010
    Posts
    3

    Re: Using match and left function to detect fraud

    =IF(LEFT(M16,1)="9","Invalid","") it works!

    Thanks everyone for your quick reply.

    If I want to detect an ssn that is either begins with digit 9 OR has 00 on the middle, what formula should I apply?

  7. #7
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Using match and left function to detect fraud

    =IF(OR(LEFT(A1)="9",MID(A1,5,2)="00"),"Invalid","Ok")

  8. #8
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Using match and left function to detect fraud

    Try

    =IF(LEFT(A2,1)="9","invalid",IF(ISNUMBER(FIND("00",A2)),"invalid",""))

    The first part of the IF statement checks for a 9 as the first character. If the first character is a 9, then "invalid" will be returned. If the first character is not a 9, another IF statement checks with the FIND() function whether the string contains two zeros in a row. FIND() returns a number or an error. So if a number is returned, the value is found. Therefore, we wrap the FIND() function in and ISNUMBER() function which will return TRUE or FALSE, depending on the result that FIND returns.

    This will find two zeros anywhere in the cell. If you are after a specific position only, then the Mid() function approach might be better.

    2014-11-23_14-22-47.png

  9. #9
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Using match and left function to detect fraud

    Quote Originally Posted by AlKey View Post
    I would disagree.
    Please elaborate.

  10. #10
    Registered User
    Join Date
    11-22-2014
    Location
    texas
    MS-Off Ver
    2010
    Posts
    3

    Re: Using match and left function to detect fraud

    Thank you Finally I can finish my homework! you guys saved my weekend!

  11. #11
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Using match and left function to detect fraud

    You're welcome and thank you for the feedback!

  12. #12
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Using match and left function to detect fraud

    Quote Originally Posted by teylyn View Post
    Please elaborate.
    I disagree with the idea that a function that works over the range of cells is less effective when applied to one cell. In fact, use of COUNTIF(s) with text strings sometimes can be only option to accomplish the task.

  13. #13
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,372

    Re: Using match and left function to detect fraud

    That's interesting. I'd like to learn more about such scenarios. Do you have an example at hand? Might make a good article on what function to use in what situation.

  14. #14
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Using match and left function to detect fraud

    Quote Originally Posted by teylyn View Post
    That's interesting. I'd like to learn more about such scenarios. Do you have an example at hand? Might make a good article on what function to use in what situation.
    I will PM you

  15. #15
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Using match and left function to detect fraud

    Using the calculation timer code found here:

    http://msdn.microsoft.com/en-us/library/aa730921.aspx

    Tested on my machine in Excel 2002 the =IF(LEFT(A2,1)="9","invalid","") formula was faster to calculate.

    =IF(LEFT(A2,1)="9","invalid","") average time to calculate 5 iterations: 0.000128 sec

    =IF(COUNTIF(A2,"9"&"*"),"Invalid","Ok") average time to calculate 5 iterations: 0.000172
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  16. #16
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Using match and left function to detect fraud

    A couple of minor tweaks...

    =IF(LEFT(A2,1)="9","invalid","")
    =IF(LEFT(A2)="9","invalid","")

    =IF(COUNTIF(A2,"9"&"*"),"Invalid","Ok")
    =IF(COUNTIF(A2,"9*"),"Invalid","Ok")

+ 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] INDEX/MATCH? - Return Value to left with IF function
    By macrav in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-02-2014, 04:05 PM
  2. [SOLVED] Index + Match to return result with Left function
    By Groovicles in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-21-2014, 02:35 PM
  3. [SOLVED] using Match, offset and Left function together
    By dowell89 in forum Excel General
    Replies: 5
    Last Post: 11-11-2013, 10:32 AM
  4. [SOLVED] IF Function referencing IsNumber, Match, Left function on separate sheets
    By Touch9713 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-12-2013, 10:09 PM
  5. [SOLVED] Left Function in Combination with IF, LOOKUP, MATCH, or INDEX?
    By superwhoever in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 10-29-2012, 01:31 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