Copy cell from 1 sheet to another sheet depending on what info is displayed in a cell

Hi Guys

It is probably a very basic request for most but i'm not exactly brilliant with excel so it's stumped me. Hope someone can help.

I use excel to keep track of football stats, in particular goal expectancies but it can be hard going inputting all the data from all the various leagues around the world. To make it easier I want to be able to type in the 2 teams playing and all the other cells in my game matchup sheet to change as per the two teams. I have managed this with all but 4 cells. Which is where I need the help. Is it possible to have a cell return the value from a particular cell on another sheet depending on what I have inputted in a particular cell on the current sheet?

To explain, on 1 sheet I have the list of teams of a particular league from say cell A5 - A28 and then in subsequent columns I have relevant info such as goals scored home and away & conceded home and away etc. On another sheet I have the facility to input 2 teams playing each other and see how their stats match up. There are 4 cells on this sheet that I want to return values from sheet 1 depending on the 2 teams inputted. So say i inputted a team from cell A17 i want to return the value from cell L17 and if the team they were playing were from cell A5 i want to return the value from L5 in another cell.

Probably very long winded and littered with inconsistencies but if anyone can understand what I mean and can help please let me know, I'd appreciate it cos it's driving my mad

Suppose you put your first team in A2 of Sheet2, then you can use this formula in B2:

=VLOOKUP(A2,Sheet1!$A$5:$L$28,12,0)

and if your second team is in C2, use this in D2:

=VLOOKUP(C2,Sheet1!$A$5:$L$28,12,0)

(which means that you can just copy/paste the first formula into D2).

Then you can just copy these formulae down if you want to enter other teams in column A and C.

Hope this helps.

Pete

Hi Pete

Thanks for helping much appreciated.

Your formula works great for the first cell but I can't get it to work with the other 3 where I need to get the cell values from W14, M14 and V17. I tried changing the L in your formula but it still returns the values from column L.

So the =VLOOKUP(F1,strengths!$A$5:$L$28,12,0) where F1 is team A and strengths is name of relevant sheet works great to return value L17 in D5 as Team A is on row 17 of my strengths sheet but when I copy and paste the formula in to E9 where I still want to return a value from row 17 but now I want column V it doesn't change and just returns the column L value.


Can you help further?

Many thanks

Column L is the 12th column of the table from column A to L, so as well as changing the L you must also change the column number that you want to get the corresponding value from. So, your table in all formulae can be $A$5:$W$28, and where you want to return from column L the third parameter of the VLOOKUP should be 12, where you want to get data from column M this is 13, and column V is 22, and so on.

Hope this helps.

Pete

Many, many thanks Pete. Works a treat now. Not only have you saved me copious amounts of data input you have taught me something too. Excellent.

Cheers

Jonny