IF function and match to return value

**rayted** · 04-23-2018, 12:08 PM

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.

**Pete_UK** · 04-23-2018, 12:19 PM

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

**Pete_UK** · 04-23-2018, 12:27 PM

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

**rayted** · 04-24-2018, 03:41 AM

This seems perfect! thanks so much Pete!

Rep added!

**Pete_UK** · 04-24-2018, 04:33 AM

Glad to be of help - cheers!

Pete

IF function and match to return value

LinkBack

Thread Tools

Rate This Thread

Display

IF function and match to return value

Re: IF function and match to return value

Re: IF function and match to return value

Re: IF function and match to return value

Re: IF function and match to return value

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Return Cell value using match function with time.

[SOLVED] INDEX/MATCH? - Return Value to left with IF function

[SOLVED] MATCH function - using COUNT to return a row#

INSERT/MATCH Function - can I match and return more than a single result?

[SOLVED] How to Return the row# of the 2nd Occurrence using MATCH( ) Function?

index and match function return #value!. Where is my mistake?

MATCH Function inconsistent in return value

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions