Excel - Dueling Excel - VLOOKUP from Outline Data - Duel 189 - Episode 2172

2,340 views · Published 10 November 2017 · 8:37 · Indexed 4 November 2026

Channel: MrExcel.com · 2017 · Education

Watch on YouTube

Microsoft Excel Tutorial: How to do a VLOOKUP when column A is in outline format

Welcome to another episode of Dueling Excel Podcast! In this episode, Bill Jelen from MrExcel and Mike Girvin from ExcelIsFun will be tackling a common issue faced by many Excel users - how to use VLOOKUP when there are blank cells in the lookup column. 

The question we received was from a group of friends who were struggling with this problem while preparing for an exam. They provided us with a sample spreadsheet and we will be using it to demonstrate the solution. The goal is to populate column B with the corresponding country names from the lookup table, but the challenge is that the lookup column has blank cells. 

The first solution that comes to mind is using the VLOOKUP function, but it only works for the first few rows and then returns an error for the rest. This is where the IFNA function comes in. It is a new function introduced in Excel 2013 and it allows us to specify a value to return if the VLOOKUP function returns an error. In this case, we want it to return the value from the cell above. 

However, there is another approach that Mike demonstrates using the LOOKUP function. This function only works for approximate match lookup and it has a unique feature where it returns the last value in the lookup column if the lookup value is larger than any value in the column. By using this function in combination with VLOOKUP, we can create a formula that always returns the correct value, even for blank cells in the lookup column. 

But, as Mike points out, this formula may be too complicated for the exam and the easier solution would be to use IFNA and VLOOKUP. However, in some cases, the extra effort to create a longer formula may be worth it, as shown in the example where there is an extra space in the lookup column. 

In conclusion, the key takeaway from this episode is to always look for the simplest solution to a problem. Don't overthink it and remember that the exam is not going to be as difficult as you may think. Thank you for watching and we'll see you in the next Dueling Excel Podcast!

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/

#excel
#microsoft
#microsoftexcel
#exceltips
#exceltricks
#excelhacks
#excelformula
#excelformulasandfunctions
#excelvlookup
#evergreen

This video answers these common search terms:
excel how to lookup when data in outline
use value from row above when data missing in this row

Table of Contents:
(00:00) Question about VLOOKUP from Outline Data
(00:21) Explanation of the question and creation of a spreadsheet
(01:03) Solution using IFNA and VLOOKUP
(02:38) Alternative solution using LOOKUP function
(06:13) Potential issue with the LOOKUP function
(08:15) Clicking Like really helps the algorithm

Advice from Bill: Anything on that test is not that difficult!
Bill's Method: Do the straight VLOOKUP
Wrap the VLOOKUP in the IFNA function. If you get an #N/A, then use the value from above
This method only works in Excel 2013 or newer. In Excel 2010, use IFERROR instead.
Mike's Method: 
Inside the VLOOKUP function, use the LOOKUP function to find the last text in an expanding range.
LOOKUP ZZZZZZ so it will return the last non-blank item
Use an expanding range to find the last text value in the column down to this point
In Mike's second method, he shows where trailing spaces will cause Bill's formula to fail.
Mike's second method uses ISBLANK and VLOOKUP to detect if a country code is missing.

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

More from this channel