Search multiple cells to display data

[leerobbo]

Alright

Sorry about the thread title it's about as much sense as I could make it sound in a short space!

Basically the data I am using is to do with the football season 2009/2010 and I am trying to create an H2H page where it will show how each team fared against each other so an example would be Fulham v Hull. What the formula would have to do is find that specific fixture then return the scoreline into the cell.

The cell that will display the scoreline is on a different sheet to the cells that have the information. The scoreline is also seperated onto two different cells so if that scoreline was 3-0 then it would be 3 in one column and 0 in another.

=IF(AND(MatchInfo!D218= A3,MatchInfo!E218=B1),MatchInfo!I218&"-"&MatchInfo!J218,"")

The above would return what I was looking for except thats because I've went out and found what cells I was looking for just to see if that formula would work. Swapping d218 for d2:d381 and so on declares the formula as wrong therefore nothing displays.

Column D in matchinfo is the home team. Column E is the away team. Column I is the home team score and column J is the away team score.

I am using Excel 2007 as well.

Thanks for any help and hopefully I've made sense explaining the problem!

 

[crook_101]

Hi,

Can you post the layout of your "results" sheet - it is poss to do but there anumber of different ways depending on what you are actually tryiing to achieve

You will find that you always get a better response if you include before and after layout.

Regards,
Ian

 

[Peter_SSs]

Welcome to the MrExcel board!

See if this helps.

Excel Workbook
	D	E	F	G	H	I	J
1	Home Team	Away Team				Home Score	Away Score
2	Team 1	Team 2				2	1
3	Team 3	Team 4				1	0
4	Team 5	Team 6				0	0
5	Team 7	Team 8				2	3
6	Team 3	Team 1				2	3
7	Team 5	Team 2				1	2
matchinfo

Formula in C2 below copied down.

Excel Workbook
	A	B	C
1	Home Team	Away Team	Score
2	Team 3	Team 1	2-3
3	Team 5	Team 6	0-0
Lookup Scores

 

[leerobbo]

The layout of the results sheet is something like

A2-A21 Team Names
B1-U1 Team Names

So the likes of A1, B2, C3, D4 etc are blank due to them having no teams related or the same teams related i.e. arsenal v arsenal.

 

[leerobbo]

Sorry for the post quickly after but it seems as if there has made a breakthrough built on what Peter_SSs posted earlier.

=SUMIFS(MatchInfo!$I$2:$I$381,MatchInfo!$D$2:$D$381,$B$1,MatchInfo!$E$2:$E$381,A3)&"-"&SUMIFS(MatchInfo!$J$2:$J$381,MatchInfo!$E$2:$E$381,A3,MatchInfo!$D$2:$D$381,$B$1)

The formula above has done the trick so thanks for all your help!

 

[Peter_SSs]

Deleted - all wrong