How to find Intersection values in Excel

Hello All. In this tutorial let’s find a simple way to find intersection values in excel. First let’s look at the example data set. Here we have grade and subject assignments as below:

We need to find out which teacher is assigned for a specified subject and specific class. Let’s see how this can be achieved.

First, let’s create a name range for both Grade value and the subject value (i.e., Grade 1 thru 5 and the 4 subjects. Select A2:E7 (in our example) -> Formulas -> Create from selection (under Defined Names section) -> check “Top Row” & “Left Column”. Shown below. More on name range here.

This would assign values to the appropriate grades and subjects under name manager. Can verify as below:

Now, all we need to do is simply type in one of the grades “Space” one of the subject and we get the intersection value. Note: all the grade and subject names are created under name manager.

Let’s add a twist to the situation. Imagine you have two drop downs – one each to select Grade and subject. The intersection has to work based on both the selections. Like this:

Formula, in column Teacher (K), would be =indirect(I3) indirect(J3).

More on indirect function here.

I hope you like this short tutorial. Please let us know your feedback that would help us in improving the content. Thanks for reading.

Leave a Reply

%d bloggers like this: