if and function

[Patcheen]

im using this formula to give me whats in fixtures and results I4 but it says

You've entered too many arguments for this function


=IF(AND('Fixtures and Results'!A4=local!A6,'Fixtures and Results'!c4=local!b6),'Fixtures and Results'!F4,local!A101,'Fixtures and Results'!I4,"")

do i need to add something to it if so what?

Tai

the result should be Faranfore

 

[Yongle]

Please explain in words
- which conditions should be satisfied
and
- which values should be returned

I will provide you with a method to convert your words into a formula

 

[Patcheen]

If game number, Game date and team in J1 (in example workbook) match then it should return answer Milford thats in the i4 in the fixture data sheet. i need to be able drag it down.

if i got Bandons home game sorted from fixtures data I4 id change the I4 to F4 for the away game.....

i am grateful to all for your help and input i know you all do so in your spare time and thank you again....i hope the work book sample was of some help.

 

[Yongle]

I want to help you but I do not yet understand what you are trying to achieve overall

Fixtures Data is presumably a list of all fixtures for the season

Are you wanting sheet Bandon to detail all the matches for Bandon in the correct sequence?
OR
Do you want a general sheet where the value in J1 is selected from a dropdown so that you can select any team and list its fixtures
(same as above, but a bit more flexible)

 

[Patcheen]

what im trying to achieve is that when the conditions match it give me the away team thats playing Bandon in d6 (populated from the fixture data sheet) answer in G6 is Milford data gotten from fixture data sheet in i4


and vice versa for who Bandon playing when they are away in game 2 Bandon are away so Bandon will be in g7 - this sheet will solely rely on the fixture data sheet. will i make a mock up of what i want might make it a small bit easier?

im open to a drop down list if poss

 

[Patcheen]

any other ideas? thanking you

 

[Yongle]

Try these:

These are ARRAY formulas and must be committed with {CTRL}{SHIFT}{ENTER}
If entered correctly, the formula in the cell is automatically enclosed in curly brackets like these { } but these cannot be typed in
The cell returns #N/A if not committed correctly

In sheet "Brandon"

Home Matches
Cell B6
=INDEX('Fixtures Data'!$F$4:$F$5000,MATCH(1,(Bandon!$B6='Fixtures Data'!$C$4:$C$5000)*(Bandon!$J$1='Fixtures Data'!$F$4:$F$5000),0))

Cell G6
=INDEX('Fixtures Data'!$I$4:$I$5000,MATCH(1,(Bandon!$B6='Fixtures Data'!$C$4:$C$5000)*(Bandon!$J$1='Fixtures Data'!$F$4:$F$5000),0))


Away Matches
Cell D15
=INDEX('Fixtures Data'!$F$4:$F$5000,MATCH(1,(Bandon!$B15='Fixtures Data'!$C$4:$C$5000)*(Bandon!$J$1='Fixtures Data'!$I$4:$I$5000),0))

Cell G15
=INDEX('Fixtures Data'!$I$4:$I$5000,MATCH(1,(Bandon!$B15='Fixtures Data'!$C$4:$C$5000)*(Bandon!$J$1='Fixtures Data'!$I$4:$I$5000),0))

 

[Patcheen]

it works a treat thank you for your help but ive only the one fixture list i showed two for sample so you could see the idea i was getting at...

is it possible to get them combined ill understand if not and thank you for your valuable time and help much appreciated

ive uploaded a sample of combined fixtures for illustaration to help wit the final outcome.

https://www.dropbox.com/s/o5ukbgh68z0u172/Sample 2.xlsx?dl=0

 

[Yongle]

So what you want is a complete fixture list (sequenced in date order) based on the value in cell J1
- complex array formulas are not my strength but I will have a go for you within the next few days

 

[Patcheen]

Your brilliant thank you so very much and thank you again for your help

 

[Yongle]

This method avoids using complicated array formulas by using a helper column in sheet "Fixtures Data"
I am using the original workbook linked by you - please use the same workbook!!
- let me know how you get on...

1 Add a worksheet and name it "Teams"

2 Copy your 20 team names from "Fixtures Data" and paste into sheet "Teams" A1:A20
- you can copy F4:F13 and paste to A1, copy I4:I13 and paste to A11
- the names must EXACTLY match so copy/paste is safer than manual typing!

3 Select "Teams" A1:A20 \ click FORMULA tab \ click DEFINE NAME \ type "ListOfTeams" in NAME box \ click OK
AND sort alphabetically: Select A1:A20 \ click DATA tab \ click SORT \ sort by column A \ order A-Z \ cick OK
(will be using this named range in Dropdown)

4 Rename sheet "Bandon" and name it "Any Team"

5 Sheet "Any Team" \ select J1 \ click DATA tab \ click DataValidation \ in ALLOW select LIST \ in SOURCE type =ListOfTeams \ click OK
(dropdown of team names should now be available)

6 Select BRANDON from the dropdown

7 Sheet "Fixtures Data" delete column J (the column not the contents)
( we are going to use this as a helper column, but at the moment it contains some merged cells which we do not want)

8 Sheet "Fixtures Data" copy I2 and paste to J2 \ amend J2 value to "HELPER"
(the copy&paste makes it easy to merged cells & match formatting)

9 Sheet "Fixtures Data" formula in J4 and copy down
=COUNTIF($F$4:F4,'Any Team'!$J$1)*(OR(F4='Any Team'!$J$1,I4='Any Team'!$J$1))+COUNTIF($I$4:I4,'Any Team'!$J$1)*(OR(F4='Any Team'!$J$1,I4='Any Team'!$J$1))

10 Most of the values in column J are zero EXCEPT J9 = 1 and J19 = 2
(these values will be used in the MATCH formula used in sheet "Any Team")
AND in G9 enter value "2", in H9 enter value "1" (ie Bandon won 2-1)
AND in G19 enter value "1", in H19 enter value "4" (ie Bandon won 4-1)

11 In sheet "Any Team" delete rows 7 to 20 (the rows NOT the contents)
(we want to remove all the merged cells etc)

12 In sheet "Any Team"
Formula in A6
=ROW()-5
Formula in B6
=INDEX('Fixtures Data'!C$4:C$1000,MATCH('Any Team'!$A6,'Fixtures Data'!$J$4:$J$1000,0))
Formula in D6
=INDEX('Fixtures Data'!F$4:F$1000,MATCH('Any Team'!$A6,'Fixtures Data'!$J$4:$J$1000,0))
Formula in E6
=IF(INDEX('Fixtures Data'!G$4:G$1000,MATCH('Any Team'!$A6,'Fixtures Data'!$J$4:$J$1000,0))="","",INDEX('Fixtures Data'!G$4:G$1000,MATCH('Any Team'!$A6,'Fixtures Data'!$J$4:$J$1000,0)))
Formula in G6
=INDEX('Fixtures Data'!I$4:I$1000,MATCH('Any Team'!$A6,'Fixtures Data'!$J$4:$J$1000,0))
Formula in H6
=IF(INDEX('Fixtures Data'!H$4:H$1000,MATCH('Any Team'!$A6,'Fixtures Data'!$J$4:$J$1000,0))="","",INDEX('Fixtures Data'!H$4:H$1000,MATCH('Any Team'!$A6,'Fixtures Data'!$J$4:$J$1000,0)))

The values in row 6 should be:
[TABLE="width: 540"]
<tbody>[TR]
[TD="class: xl80, width: 64, align: right"]1[/TD]
[TD="class: xl82, width: 104, align: right"]10/09/2017[/TD]
[TD="class: xl78, width: 5"][/TD]
[TD="class: xl79, width: 117"]Bandon[/TD]
[TD="class: xl79, width: 64, align: right"]1[/TD]
[TD="class: xl81, width: 5"][/TD]
[TD="class: xl79, width: 117"]Milford[/TD]
[TD="class: xl79, width: 64, align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]

Copy/paste (or drag) A6:I6 down the rows and should match table below
The values now look like this:
[TABLE="width: 549"]
<tbody>[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]10/09/2017[/TD]
[TD][/TD]
[TD]Bandon[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD]Milford[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]12/09/2017[/TD]
[TD][/TD]
[TD]Milford[/TD]
[TD="align: right"]1
[/TD]
[TD][/TD]
[TD]Bandon[/TD]
[TD="align: right"]4
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[TD="align: center"]#N/A[/TD]
[TD="align: center"]#N/A[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

N/A values will be replaced with proper values when you add more data into Sheet "Fixtures Data"