Be able to use external data sources within functions

📚 What you’ll learn: How to pull data from outside your spreadsheet (like a CSV file, a database, or a web page) and use that data inside formulas such as VLOOKUP, INDEX/MATCH, and SUMIF.

🎯 Be able to use external data sources within functions.

Think of your spreadsheet as a smart library. An external data source is like a book that lives in a different room – it contains information you can borrow and use in your calculations.

• 📁 CSV/Excel files – a simple table of data.
• 🗄️ Database tables – data stored in SQL or Access.
• 🌐 Web queries – live data from a website.

1. Go to Data ► Get Data.
2. Select the source type: From File → CSV, From Database → SQL Server, or From Web → URL.
3. Choose the file or enter the URL, then click Load.
4. The data appears in a new sheet called ExternalData (you can rename it).

⚙️ Tip: After loading, right‑click the table and choose Refresh to update it whenever the source changes.

Let’s say you have a CSV file named Grades.csv with columns Student and Score.

To find a student’s score in your main sheet, you can use:

=VLOOKUP(A2, 'Grades.csv'!$A$1:$B$100, 2, FALSE)
  

Here, A2 contains the student’s name. The formula looks up that name in the external table and returns the score from column 2.

If you prefer INDEX/MATCH for flexibility:

=INDEX('Grades.csv'!$B$1:$B$100, MATCH(A2, 'Grades.csv'!$A$1:$A$100, 0))
  

And for conditional sums:

=SUMIF('Grades.csv'!$A$1:$A$100, "Math", 'Grades.csv'!$B$1:$B$100)
  

📊 Use this table as your Grades.csv source.

• 🔗 Check links: Ensure the external file path is correct and the file is accessible.
• 🔄 Refresh data: Always refresh before calculating to use the latest values.
• ⚠️ Absolute references: Use dollar signs ($) to lock ranges when copying formulas.
• 📈 Test with small data: Start with a few rows to verify the formula works before scaling up.
• 🧩 Remember the syntax: VLOOKUP(lookup_value, table_array, col_index, FALSE) – the last argument must be FALSE for exact match.

🚀 By importing external data and using it in functions, you can keep your spreadsheets dynamic and up‑to‑date. Think of it as giving your spreadsheet a remote control that pulls fresh information whenever you need it.

?? Practice importing a CSV, writing a VLOOKUP, and refreshing the data. You’ll be ready for the exam and for real‑world projects!