Excel - After Embedding Lookup Table in an Array Constant, Write It back to Grid - Episode 1418
746 views · Published 31 August 2011 · 3:03 · Indexed 16 November 2026
Channel: MrExcel.com · 2011 · Education
Microsoft Excel Tutorial: How to extract an Array Constant from a Formula Back to the Grid. Welcome to the MrExcel podcast. In this episode, we will be discussing the concept of "Unwinding Array" in Excel 2010. This is a common issue that many Excel users face, and we will be exploring a simple solution to this problem. Have you ever received a worksheet from a co-worker that contains a vlookup formula with the entire array embedded in it? While this may seem like a convenient solution, it can become a hassle when you need to convert the data back to its original form. In this video, we will show you how to easily convert the data back to its original form using a few simple tricks. First, we will select the characters from the first curly bracket to the last curly bracket in the formula and copy them to the clipboard. Then, we will go back to our worksheet and select two columns by 50 rows (or 51 rows if you want to include Washington DC). We will then paste the array in text form and press [control shift enter] to convert it back to an array formula. This will give us the entire array in our worksheet, making it easier to work with. But what if we don't want to use the vlookup formula and the embedded array? No problem! We can easily turn the address back into an array by selecting the characters and pressing [F9], followed by [Enter]. This will convert the address back to an array, allowing us to delete the embedded table and still have our formula work correctly. In this episode, we have shown you how to unwind an array in Excel 2010 using a few simple tricks. We hope this will help you save time and frustration when working with embedded arrays in your worksheets. Thank you for watching and don't forget to tune in for more helpful tips and tricks from MrExcel. 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) Unwinding Array (00:10) Explanation of Worksheet Example (00:21) Converting from State Names to Codes (00:44) Copying and Pasting Array Formula (01:18) Using Paste Special Values (01:42) Converting from Codes to State Names (02:09) Building the Formula (02:24) Turning Address into Array (02:47) 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: Array formula Convert state codes to state names Excel tricks Learn Excel Ohio to OH conversion Paste special values Spin table back Vlookup Vlookup table Washington DC missing in array Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1153096/ A VLOOKUP to convert State Codes to State Names all in an Array Formula - and no backward compatibility! Today, in Episode #1418, Bill shows us how to get this issue fixed along with a brief, but informative look at a few ways to work with VLOOKUP.
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