2 Way Lookup using Index Match

I taught an Excel functions class at the Indiana CPA Society last week and thought I would share one of the examples we went over. We used the INDEX MATCH  function to create a 2 way lookup.
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

In this example, we wanted to lookup Unit Sales for a specified month (I1) and for a specified Product Line (I2).

We need Excel to match the contents of I1 which is the month of July and the contents of I2 which is TV product line with the table in cells A2.E13 and find the intersection point and return the answer.

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
19.Click OK

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