I have a spreadsheet containing named values of football clubs in the leftmost column and named column titles across the top.

How can I insert the value from a certain cell in another sheet?

For example, I want to insert Aston Villa's Ranking of TE in a cell on another worksheet. Is this possible and how can I do it?

Also, If I have a list of the same names i.e Aston Villa on the separate worksheet, can I have drag down to automatically fill the values based on the name of the club in the same worksheet?

enter image description here]


I have taken this dummy data as an example.

| Club       | Transfer Summer | Transfer Winter | Average |
| Aston      | 23              | 23              | 94      |
| Birmingham | 32              | 29              | 53      |
| Bolton     | 96              | 72              | 80      |
  1. Name the range.

Select all your data then click Formulas > Name Manager > New. I called my example range "football"

  1. Enter your VLOOKUP

To look up the Transfer Winter number for Birmingham, we would use this formula;


This would return 29.

VLOOKUP needs to know

  • what it is looking for
  • the range of cells to look in
  • which column to return
  • whether to use a range lookup, usually FALSE

If you had a list of clubs in the A col of another sheet, you could also do;


This would allow you to drag down and get the values for those clubs.

  • Thanks to bertieb for making my formatting a bit clearer :) – James Geddes Jun 24 '19 at 16:13
  • Thank you very much for your help :) – Callum Jun 25 '19 at 9:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.