That's where MATCH comes in. MATCH searches for a value in a range of cells and returns the location of what's matched, using the following format:
(Match type can either be 0 for exactly equal, 1 for largest value less than or equal to what you're searching for or -1 for the smallest value that is greater than or equal to your lookup value.)
So, if you wanted to find the location of a cell in column B that was exactly 999, you could use:
=MATCH(999, B2:B79, 0).
And, so the combination: MATCH, looking for a specific value based on a search term, returns a cell location; and INDEX needs a location as its second formula argument.
10. Watch a formula be evaluated step by step (for Windows only). Have a complicated formula? If you want to see how it gets evaluated, go to Formulas > Evaluate Formula to see the calculations run step by step.
11. Import and refresh data from the Web into Excel. This works best when you've got well-formatted HTML tables on a Web page; with more free-form text (or even poorly formatted tables), you'll need to do a fair amount of additional editing to get your data into a form you can analyze.
With that warning in mind, if you want to pull an HTML table from the Web into Excel, head to the Data tab on Excel for Windows and select: New Query > From Other Sources > From Web
Enter the URL of the appropriate Web page. Excel will look for and list available HTML tables on that page. Click on a table to see a preview; when you find the one you want, click Load.
Why not just copy and paste a well-formatted HTML table into Excel? If the data updates frequently, you can easily refresh it by right-clicking in the table and selecting Refresh instead of having to copy and paste new data.
For more on the conference, check out the Microsoft Data Insights videos on YouTube.
Excel tips resource list
Tips and Tricks for Working With Data in Excel
Matt Fichtner and Chris Gross
Cool Tips And Tricks With Formulas in Excel
Microsoft Data Insights Summit 2016
Unpivot columns (Power Query)
Excel 2010 cheat sheet
Preston Gralla and Rich Ericson
Your Excel formulas cheat sheet: 15 tips for calculations and common tasks
Sign up for CIO Asia eNewsletters.