How to look up based on two criteria

[darylpaskell]

First time post here but wondered if you could help me.

I compile spreadsheets that document teams progress during a season; each team has their own tab which lists their fixtures results etc. My problem evolves from me trying to make this process more computerized and to remove data entry.

In all my other spreadsheets, I have access to each teams fixtures by date and thus could use the sumproduct formula to search for the team it played against and pull out the relevant information but in this instance, I don’t have access to those, so I'm having update each teams results on a round by round basis.

I've created a master tab which all the fixtures are entered into and I used this as the basis of my look up but with me not having access to the fixtures, i want to create a look-up that searches for the earliest date and then picks out the team it's playing against.

So there's two things I want to happen: 1) to look up in the master spreadsheet the earliest date and put it into columna a and then once that has been found, in the cell adjacent to it, I want it to search for the team name AND the date to draw out it's opponent

I hope I've explained that as clearly as possible. I've attached the workbook to hopefully make what i'm trying to solve a bit clearer.

[martindwilson]

you need to attach both workbooks for the links to work

[darylpaskell]

Hello Martin,

Thanks for replying. That is the only workbook I'm using and require but perhaps I didn't explain it as clearly as I first hoped.

In the Chivas tab, i want to produce a look up -if possible, in cell A5 that searches for Chivas in the the results tab and then produce another lookup in cell B5 that searches for the team it's playing in column D in the resultz tab based on that date. I've been attempting to use sumproduct formula but to no success

I hope i've made that a lot clearer than I first did