Look Who it Was Random video

Excel - Build an Excel Formula that References Another Worksheet - Episode 672

730 views · Published 19 February 2009 · 2:17 · Indexed 20 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial: Build an Excel Formula that References Another Worksheet.
Welcome back to the MrExcel netcast! In today's video, we'll be discussing how to do a VLOOKUP from one worksheet to another. This is a common task in Excel, but it can be tricky to remember the syntax for referencing another sheet. So, I'll be sharing my go-to method for easily referencing offsheet data.

To start, let's say we need to do a VLOOKUP for the cost in cell C101. Instead of typing out the formula, I always prefer to use the mouse to point to the data I need. So, I'll hit the F4 key three times to lock the column, then add a comma. Now, we need to specify the lookup table on the other sheet. To do this, I'll click on the tab for the "Last Year" sheet and highlight the entire range. Don't forget to hit the F4 key once to make the reference absolute. Then, we'll add a comma and specify which column we want to return and set the range lookup to FALSE.

If you take a look at the formula bar, you'll see that Excel has automatically built the syntax for us. We need to add an apostrophe, the sheet name, another apostrophe, and an exclamation point before the address. Once we hit "Enter," we can copy the formula down and it will work for all the cells in that column. However, there's a shortcut that can make this process even easier.

If your sheet names have spaces in them, like "Last Year," you'll need to remember to add the apostrophes. But, if you rename the sheet without spaces, like "LastYear," the formula becomes much simpler. You can just add the sheet name, an exclamation point, and the address. No need for apostrophes at all! This is a great time-saving tip that can make referencing offsheet data a breeze.

Thanks for watching this netcast from MrExcel! I hope this tip helps you easily reference data from one worksheet to another. Don't forget to hit the like button and subscribe for more Excel tips and tricks. See you next time!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/


How can you do a VLOOKUP from one worksheet to another worksheet? It is simple to do, but hard to remember the syntax. In Episode 672, I'll show you the easy way to enter these formulas.

Table of Contents
(00:00) VLOOKUP from one sheet to another
(00:44) Pointing to another sheet while building the formula
(01:07) Excel inserts apostrophes if needed
(01:31) Avoiding apostrophes with careful sheet naming
(01:43) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial 

This video answers these common search terms:
How to reference another sheet in a VLOOKUP formula
Netcast from MrExcel about referencing sheets in formulas
Pointing to a range in another worksheet using the mouse
Remembering how to reference sheets in formulas
Setting up links between worksheets
Shortcut for referencing sheets with spaces in their names
Simplified syntax for referencing sheets without spaces in their names
Syntax for referencing another sheet in VLOOKUP
Tips for successful sheet referencing in formulas
Using apostrophes and exclamation points in sheet references
Using the F4 key to lock columns in VLOOKUP
VLOOKUP from one worksheet to another worksheet

Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152227/

More from this channel