Truth in advertising. Excel Video 69 is more complicated than most of these videos. I have a detailed table of partner’s equity transactions during the year and want to write one formula to pull specific rows and columns out of the detailed table to get a summary of what percentage of the practice each partner owns at the end of each quarter during the year. One way to get Excel to pull a specific horizontal and vertical coordinate from a table in one formula is to use VLOOKUP twice in the same formula.
The first VLOOKUP in this example looks up the text describing the end of each quarter to get on the correct row of the detail table. The second VLOOKUP nests inside the first VLOOKUP and tells Excel which column to return when it finds the correct row. To make the second VLOOKUP work, you’ll see I have a separate table that tells Excel which column I want based on which partner Excel’s looking for.
Believe it or not this example is actually based on a spreadsheet I created for a group of physicians I work with. It may seem complicated at first, but if you can use two VLOOKUPs in the same formula, you can do a lot more with VLOOKUP.
Stay tuned. Excel Video 70 starts a discussion of charts and graphs in Excel 2007. Thanks for watching.
