Microsoft Excel Tutorial: VLOOKUP Range: How to Use VLOOKUP to Return a Range in Excel.

Welcome to the MrExcel Podcast. In this episode, we will be discussing VLOOKUP Range and how to use it in Excel. I’m Bill Jelen, also known as MrExcel, and I’m excited to share this useful tip with you.

Today’s question comes from Jer, who wants to know if it’s possible to return a range using VLOOKUP. Well, the answer is yes! There is an obscure version of VLOOKUP that allows you to do just that. Let’s take a look at how it works.

First, we need to turn our data into a table. I’ll use the Data Text to Columns feature and select Fixed Width. Then, I’ll select the starting number and the description as the only columns we need. Next, we’ll use the VLOOKUP formula, looking up the 4750 value in our table. We’ll lock the table range using F4 and select the third column for the return value. Since we are doing a range lookup, we can either put /”true/” or leave it blank for Excel to automatically do the lookup. And just like that, we have our desired result – 153 for fixed assets.

But what about those pesky leading zeros? Well, we can easily format the cells using the Custom option and entering 0000. And don’t worry about the postal code for Portugal, it’s just a funny coincidence. And just to prove that we don’t need the extra column, we can delete it and change the column number in our formula to 2. As long as our data is sorted, this formula will work like a charm.

I hope this tip helps you in your Excel journey. Thank you for tuning in to another MrExcel netcast. Don’t forget to subscribe to our channel for more useful 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/

Table of Contents:
(00:00) VLOOKUP Range Tutorial
(00:20) Using an Obscure Version of VLOOKUP
(00:32) Creating a Table with Data Text to Columns
(00:44) Using VLOOKUP with a Range Lookup
(01:02) Explanation of VLOOKUP Parameters
(01:21) Using TRUE for Range Lookup
(01:37) Formatting Cells to Show Leading Zeros
(02:06) Deleting Extra Column and Final Thoughts
(02:16) 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:
Cost centers
Data text to columns
Excel learning
Fixed assets
Leading zeros formatting
Range lookup
Sorting data
Table range
VLOOKUP function
VLOOKUP Range

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

Todays question involves doing a Range Lookup using a VLOOKUP Formula. Bill shows us how to set this up based on the Data Range provided and explains why today’s VLOOKUP will be ending with a ‘TRUE’ statement instead of the usual ‘False’. See how and why its done in Episode #1570.

…This a the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill’s book! http://www.mrexcel.com/learn2010/LE2010.html

/”The Learn Excel from MrExcel Podcast Series/”

Visit us: MrExcel.com for all of your Microsoft Excel Needs!

Please take the opportunity to connect and share this video with your friends and family if you find it useful.