+ Reply to Thread
Results 1 to 5 of 5

IF function and match to return value

  1. #1
    Forum Contributor
    Join Date
    11-13-2015
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    736

    IF function and match to return value

    Hi guys,

    I`m trying to automate a process/quicken it for some predictions I capture from friends for a football competition.

    Using my table attached, can someone help me with writing an IF formula in cell F2 to say:
    IF C2 matches B2, return the value 10, if C2 does not match B2, return the value 0

    In G2 if the first number of E2 matches the first number of D2, return 5, if the first number of E2 does not match the first number of D2, return 0

    In H2 if the second number of E2 matches the second number D2, return 5, if the second number of E2 does not match the second number D2, return 0

    I`d really appreciate the help guys.
    Attached Files Attached Files
    Thanks,

    R.



  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,520

    Re: IF function and match to return value

    Use the following formulae in the cells stated:

    F2: =IF(B2=C2,10,0)

    G2: =IF(LEFT(D2,FIND("-",D2)-1)=LEFT(E2,FIND("-",E2)-1),5,0)

    H2: =IF(MID(D2,FIND("-",D2)+1,10)=MID(E2,FIND("-",E2)+1,10),5,0)

    then copy down as required.

    Hope this helps.

    Pete

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,520

    Re: IF function and match to return value

    On second thoughts, these would be more robust:

    F2: =IF(OR(B2="",C2=""),"",IF(B2=C2,10,0))

    G2: =IFERROR(IF(LEFT(D2,FIND("-",D2)-1)=LEFT(E2,FIND("-",E2)-1),5,0),"")

    H2: =IFERROR(IF(MID(D2,FIND("-",D2)+1,10)=MID(E2,FIND("-",E2)+1,10),5,0),"")

    and then you can copy them down as far as you like below the data that you have entered, in readiness for new data - they will return blanks until you enter appropriate data.

    Hope this helps.

    Pete

  4. #4
    Forum Contributor
    Join Date
    11-13-2015
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    736

    Re: IF function and match to return value

    This seems perfect! thanks so much Pete!

    Rep added!

  5. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,520

    Re: IF function and match to return value

    Glad to be of help - cheers!

    Pete

+ 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] Return Cell value using match function with time.
    By megamef in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-09-2014, 06:46 AM
  2. [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
  3. [SOLVED] MATCH function - using COUNT to return a row#
    By vba_madness in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-09-2014, 08:13 AM
  4. INSERT/MATCH Function - can I match and return more than a single result?
    By nickwee in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-24-2014, 06:02 AM
  5. [SOLVED] How to Return the row# of the 2nd Occurrence using MATCH( ) Function?
    By htawfik in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-08-2014, 12:10 PM
  6. Replies: 2
    Last Post: 06-14-2010, 02:04 AM
  7. MATCH Function inconsistent in return value
    By Moose8 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 07-12-2008, 01:06 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