Look Who it Was Random video

Excel - Using Excel Named Ranges to Simplify VLOOKUP to Another Worksheet - Episode 675

754 views · Published 19 February 2009 · 2:06 · Indexed 20 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial: Using Excel Named Ranges to Simplify VLOOKUP to Another Worksheet.

Welcome back to the MrExcel netcast! In today's episode, I, Bill Jelen, will be discussing the benefits of using named ranges in Excel. If you caught Monday's episode, you may remember me talking about the difficulties of setting up a VLOOKUP formula and the confusion that can arise with the use of apostrophes and exclamation points. Well, thanks to a great suggestion from Paul in Derby, UK, I have a solution that will make your life a whole lot easier.

So, what exactly is a named range? It's simply a way to assign a name to a specific range of cells in your worksheet. This can be incredibly useful when working with large amounts of data or when referencing data on another worksheet. To set up a named range, all you have to do is select the desired cells, click on the name box (located to the left of the formula bar), and type in a name without any spaces. For example, I could name my selected cells "MyData" and hit enter.

Now, let's say I want to use this named range in a VLOOKUP formula on a different worksheet. Instead of having to remember the exact syntax and placement of apostrophes and exclamation points, all I have to do is type in the name of my named range, "MyData". Easy, right? And if you happen to forget the name of your named range, simply press the F3 button to bring up the "Paste name" box and select the desired name from the list.

I want to give a big thank you to Paul for sharing this helpful tip with us and to all of you for tuning in. Don't forget to join us next time for another informative netcast from MrExcel. And as always, happy Excel-ing!

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/


Today we revisit Episode 672. Rather than using the difficult syntax from that episode, Paul from Darby suggests using a named range. Episode 675 shows you how.

Table of Contents:
(00:00) Introduction to setting up a VLOOKUP on another worksheet
(00:26) Difficulty with syntax and a solution from Paul in the UK
(00:40) Setting up a named range on the data worksheet
(01:03) Using the named range in the VLOOKUP formula
(01:25) Using F3 to get a list of named ranges
(01:38) Benefits of using a named range to avoid entering special characters
(01:48) 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:
Excel tips and tricks
Excel tutorial for beginners
F3 shortcut for paste name box in Excel
How to avoid entering apostrophes and exclamation points in VLOOKUP
How to use VLOOKUP in Excel
Setting up a named range in Excel
Syntax for VLOOKUP formula
Tips for working with worksheets in Excel
Using named ranges in Excel
VLOOKUP tutorial

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

More from this channel