It was a great class. If you missed it, look for it next year- It is called Functions Every CPA Needs To Know.
If you don't have time for the class, I am putting together the course and will be offering a self study version shortly so check at http://cpaselfstudy.com/category_14/Excel.htm end of October.
My Dashboards self study course is almost done. It should be avaible in another week or so as the CPE piloting is basically done.
2 Way Lookup
If I asked you to look at A1.E13 and to find the unit sales for TVs for the month of July, you would look down Column A until you found July and then you would look across the columns until you found TV and you would find the intersection point of 763 which is the answer. This is that a 2way lookup does.
1. Click in cell I3
2. Select the Index function from the Lookup category
3. Click OK
4. Enter A1:E13 as the Array
5. Click in the Row_number section of the dialog box
6. Click in the Name box and click on the Match function
7. Enter I1 as the Lookup Value
8. Enter A1:A13 as the Lookup_array
9. Enter 0 as the Match_Type
10. Click OK
11. You will have an error message – but don’t panic
12. Go up to the formula bar and click on the fx button to bring the Index dialog box back
13. Click OK
14.Click in Col_num in the Index dialog box
15.Click in the Name box and select the Match function
16.Enter I2 as the Lookup_Value
17.Enter A1.E1 as the Lookup_Array
18.Enter 0 as the Match Type
Notice the 8 and the 4 in the dialog box (over on the right)- the 8th row and the 4th column intersect at cell D8. If you use a Data Validation drop-down for I1 and I2 so much the better.
For other software tips and articles, go to http://cpaselfstudy.com/pages/SoftwareTips.php