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
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
-
2:13
Excel Calculate Decimal Hours from two Time Cells - Episode 902
-
3:57
Excel 2007 - Color of Selected Sheets is too much like unselected sheets - Episode 907
-
2:48
Excel - Adding Equals Icon to Excel QAT - Build Formula with Mouse - Episode 911
-
3:30
Excel Grouping Percentages into Buckets for a Pivot Table - Method 2 - Episode 913
-
2:04
Excel Change Color of Selected Cells - Episode 914
-
4:38
Excel - Auto Numbering a List with Frequent Inserts & Deletes - Episode 916
-
2:00
Excel Hiding Data in Plain Site - Episode 919
-
2:20
Excel - Bring Back The Full Excel 2003 Dialogs In Excel! - Episode 920