Blue Flower

How to return a result from the intersection of the row header and the column header

Surfing the Internet and visiting forums whose theme is EXCEL, I've often seen issues related to Rows header and Columns header, the result should be the intersection of these two conditions.So these are the two terms 'Header of Rows' and 'Header of Columns'. In this Excel tutorial I will show you the collection of Excel formulas that as a result return the intersection of the Column headers and the Row headers. In the image below, notice that I have two options for setting conditions.

  1. The first option: two conditions are in cells
  2. The second option: the two conditions are in the ComboBox menu

Also, note that I have presented two variants of the source data.

  1. In the header of the rows and columns are the values (numbers)
  2. In the header of the rows and columns there is text

To solve the task if we have two specific conditions as in this case, we can use multiple combinations of nested formulas. If we look at the basics of Values/Text then we can see that we can use the same formulas to return results from the intersection of columns and rows, but there is little difference.

The column headings in the columns are VALUE (numbers)

In this first example, notice that the rows and column headers have values (ie numbers). For Option 1, we use the numbers entered in 'B16' and 'C16' cells. Results for these two conditions are shown in the 'D16' cell and down.

The row and column header is a values

In the images above, notice the following: All of the formulas shown below return the same result for two conditions.

OPTION 1

The formula in 'D16' cell is the following:
=INDEX(B2:J12;MATCH(B16;A2:A12;0);MATCH(C16;B1:J1;0))

The formula in 'D17' cell is the following:
=SUMPRODUCT(B2:J12*(A2:A12=B16)*(B1:J1=C16))

The formula in 'D18' cell is the following:
=INDEX(B2:J12;RANK(B16;A2:A12;1);RANK(C16;B1:J1;1))

The formula in 'D19' cell is the following:
=HLOOKUP(C16;A1:J12;MATCH(B16;A2:A12)+1;FALSE)

The formula in 'D20' cell is the following:
=OFFSET($A$1;MATCH(B16;A2:A12;0);MATCH(C16;B1:J1;0))

The formula in 'D21' cell is the following:
=INDEX(A1:J12;MATCH(B16;INDEX(A1:J12;;1);0);MATCH(C16;INDEX(A1:J12;1;);0))

How to return an intersection if the two conditions 'Values' are in the ComboBox menu

OPTION 2

In this option the conditions are set in the ComboBox menu. The 'Helper' column serves as an auxiliary column for the Combo Box (yellow color) Criteria2 drop-down menu. So to have a drop-down menu in the ComboBox, we have the original data placed in the vertical position. You can hide this 'helper' auxiliary column to be invisible. At the end of this Excel example tutorial, see a more detailed explanation of how to create a ComboBox and how to set up basic settings. The conditions are in 'F16' and 'G16' cells (these cells are not visible because they are covered with the ComboBox frame). Notice the difference in some formulas when the original data is a value or a text.

The formula in 'H16' cell is the following:
=INDEX(B2:J12;MATCH(F16*1;A2:A12;0);MATCH(G16*1;B1:J1;0))

The formula in 'H17' cell is the following:
=SUMPRODUCT(B2:J12*(A2:A12=F16*1)*(B1:J1=G16*1))

The formula in 'H18' cell is the following:
=INDEX(B2:J12;RANK(F16;A2:A12;1);RANK(G16;B1:J1;1))

The formula in 'H19' cell is the following:
=HLOOKUP(G16*1;A1:J12;MATCH(F16*1;A2:A12)+1;FALSE)

The formula in 'H20' cell is the following:
=OFFSET($A$1;MATCH(F16*1;A2:A12;0);MATCH(G16*1;B1:J1;0))

The formula in 'H21' cell is the following:
=INDEX(A1:J12;MATCH(F16*1;INDEX(A1:J12;;1);0);MATCH(G16*1;INDEX(A1:J12;1;);0))

The Headers of Rows and Columns is TEXT

This second example is similar to the first, the difference is that there is text in the header of the rows and columns. In principle we can use the same formulas as in the previous case but with small differences in the formula.

The row and column header is a text

Notice that there is no difference in formulas when the source data is text. Of course, you have to keep in mind if you have a lot of data, which are of the above formulas, the Excel Volatile functions, because you can slow down Excel when calculating.

OPTION 1

The formula in 'D16' cell is the following:
=INDEX(B2:J12;MATCH(B16;A2:A12;0);MATCH(C16;B1:J1;0))

The formula in 'D17' cell is the following:
=SUMPRODUCT(B2:J12*(A2:A12=B16)*(B1:J1=C16))

The formula in 'D18' cell is the following:
=VLOOKUP(B16;A2:J12;MATCH(C16;A1:J1;0);0)

The formula in 'D19' cell is the following:
=HLOOKUP(C16;A1:J12;MATCH(B16;A2:A12)+1;FALSE)

The formula in 'D20' cell is the following:
=OFFSET($A$1;MATCH(B16;A2:A12;0);MATCH(C16;B1:J1;0))

The formula in 'D21' cell is the following:
=INDEX(A1:J12;MATCH(B16;INDEX(A1:J12;;1);0);MATCH(C16;INDEX(A1:J12;1;);0))

How to display an intersection if two conditions are set to 'Text' in the 'Combo Box menu'

OPTION 2

In this option, the conditions are set to the Combo Box menu. The 'Helper' column serves as the auxiliary column for the drop-down menu for the Combo Box (yellow color) Criteria2.

The formula in 'H16' cell is the following:
=INDEX(B2:J12;MATCH(F16;A2:A12;0);MATCH(G16;B1:J1;0))

The formula in 'H17' cell is the following:
=SUMPRODUCT(B2:J12*(A2:A12=F16)*(B1:J1=G16))

The formula in 'H18' cell is the following:
=VLOOKUP(F16;A2:J12;MATCH(G16;A1:J1;0);0)

The formula in 'H19' cell is the following:
=HLOOKUP(G16;A1:J12;MATCH(F16;A2:A12)+1;FALSE)

The formula in 'H20' cell is the following:
=OFFSET($A$1;MATCH(F16;A2:A12;0);MATCH(G16;B1:J1;0))

The formula in 'H21' cell is the following:
=INDEX(A1:J12;MATCH(F16;INDEX(A1:J12;;1);0);MATCH(G16;INDEX(A1:J12;1;);0))

How to create the ComboBox drop-down menu in Excel

In cases when using the ComboBox drop-down menu, you should know that the source data for the 'drop-down menu' should be placed vertically in the column. That is why I used the 'helper' column in the examples above. In the images above, you do not see 'F16' and 'G16' cells, because they cover the ComboBox frame. In the picture below, notice the relevant cells and the ComboBox frame. ComboBox frames are connected to cells. So ComboBox1 is connected to 'F16' and ComboBox2 is connected to 'G16' cell.

To set up the ComboBox drop-down menu in Excel Worksheet, do the following:

  1. Click the 'Developer' tab
  2. Click the 'Design Mode' button
  3. Click the 'Insert' button
  4. Click the second button in the order of 'Combo Box' in the 'Control ActiveX Controls'
  5. Click on the Worksheet cell where you want to create a Combo Box drop-down menu (Drag-And-Drop method create the ComboBox frame).
  6. Right-click on the ComboBox frame (you must have the 'Design Mode' command active)

You came across the Properties dialog box where you can change the basic settings (LinkedCell, ListFillRange etc). Notice that the value or text you choose in the Combo Box menu is automatically copied to 'LinkedCell' in this case 'F16' cell, remembering this cell is our condition in the formulas. You need to move the ComboBox frame to overlay the 'F16' cell, to visually invisible.

Notice that 'LinkedCell' cells like 'F16' and 'G16' have the format as text.
When it comes to terms like Values/Numbers then I'm because of this format as a text, in the above formulas we added multiplication with number 1 (...MATCH(F16*1;INDEX...).

Creating a ComboBox in Excel

Of course, you can always use the Data Validation drop-down menu (maybe it's easier for you) instead of the Combo Box.

Creating a formula when the result is on another worksheet

Surfing the Internet, I noticed that many users of Excel have problems when you need to create a formula that is located on another worksheet and conditions on the third or the same worksheet. This should not be a problem.

My advice:

  1. Create the formula on the same worksheet that contains the original data.
  2. After completing formulating the formula, using the 'Cut/Paste' method, move the results to the second worksheet
  3. Excel will automatically add the workbook name to the beginning of the cell range or cells

To make it even easier to do so, you can naming cell ranges or just a cell. Wherever you created the formula and if you use a named cell ranges, there will be no problems.
[eg: =VLOOKUP(A2;Sheet1!$R$14:$U$19;2;FALSE) is the same as =VLOOKUP(A2;namedData;2;FALSE)]

For example the above formulas, the same formulas may be on another worksheet as follows:
(Values! Is the name of the worksheet that contains the source data)

=INDEX(Values!B2:J12;MATCH(A3;Values!A2:A12;0);MATCH(B3;Values!B1:J1;0))
=SUMPRODUCT(Values!B2:J12*(Values!A2:A12=A3)*(Values!B1:J1=B3))
=INDEX(Values!B2:J12;RANK(A3;Values!A2:A12;1);RANK(B3;Values!B1:J1;1))
=HLOOKUP(B3;Values!A1:J12;MATCH(A3;Values!A2:A12)+1;FALSE)
=OFFSET(Values!$A$1;MATCH(A3;Values!A2:A12;0);MATCH(B3;Values!B1:J1;0))
=INDEX(Values!A1:J12;MATCH(A3;INDEX(Values!A1:J12;;1);0);MATCH(B3;INDEX(Values!A1:J12;1;);0))

I hope that this collection of Excel formulas, with two conditions, relating to display results from the intersections of the header rows and columns, make it easy to solve your problems and tasks while using Excel.
Finally, in the attachment of this tutorial, you have the Download file option on which I did this tutorial.

Pay attention, you may need as a delimiter of arguments formula use a comma (,) instead of a semicolon (;).
Copy formula to Notepad and with Find/Replace change semicolon to comma.