How to use vlookup in excel 2016 step by step with example
![how to use vlookup in excel 2016 step by step with example how to use vlookup in excel 2016 step by step with example](https://cdn.ablebits.com/_img-blog/vlookup-examples/excel-vlookup-multiple-criteria.png)
In other words, it automatically returns an array of values.
![how to use vlookup in excel 2016 step by step with example how to use vlookup in excel 2016 step by step with example](https://support.content.office.net/en-us/media/4c03d834-100f-4a35-8896-3942dfe127ce.png)
Thus, using the range E2:E8 for the first parameter means that VLOOKUP returns 7 values. In fact, when you use a range of cells in the very first parameter for VLOOKUP, it will now return a value for each cell in that range. This error occurs because the VLOOKUP formula can now return more than a single value. The same simple VLOOKUP formula in the latest version of Excel. Now, let's look at what happens if you create the same workbook, using the same formulas, in the version of Excel provided with Office 365. You get the desired results because the VLOOKUP function returns a single value from the table. Note in this example that the VLOOKUP formula in cell F2 (shown in the Formula bar because cell F2 is selected) is copied down to the range F2:F8. This screen shot was taken using an Excel 2010 system, but it would work the same if you looked at it in Excel 2016 or even Excel 2019. When you open this workbook in an older version of Excel (2019 or earlier) you get great results. Then, to the right of this, you enter some items and use a VLOOKUP function to return the prices associated with each of those items. Let's say you have a worksheet that lists items and their prices in a simple two-column set of data.
![how to use vlookup in excel 2016 step by step with example how to use vlookup in excel 2016 step by step with example](https://www.excel-easy.com/examples/images/vlookup/vlookup-result.png)
The answer is to understand what Excel is now doing as it calculates and then modify your formulas accordingly. Quite honestly, the answer isn't to disable #SPILL! errors there really is no way to do so. If the array of returned values won't fit in the available space, you get the new #SPILL! error. If you create lots of formulas in Excel, you'll want to do so, though-the change to the program means you must understand it, eventually.Įssentially, Microsoft did away with the concept of array functions (though they will still work), instead allowing almost all functions, including VLOOKUP, to return an array of values. The article is rather long you'll want to set aside some time to digest the information it contains. Here's one article that provides some great information on the change: This was a HUGE change, and you may have read about it elsewhere. In the very near past, Microsoft changed how it calculates worksheets. He wonders if there is a way to turn this "feature" off. Jan knows it would be easy to sort and/or to remove the #N/A that came from the previous VLOOKUP, but this error is causing him a lot of extra time and effort. He is now unable to use VLOOKUP with a filter. With it came a #SPILL! error when using VLOOKUP, which he uses a LOT. Jan's company recently upgraded to Office 365 ProPlus.