Our Recommendation for You Search your Query, You can find easily. for example search by book name or course name or any other which is related to your education

Followers

Find Intersection of Row and Column

How to find the intersection of Row and Column if we have two conditions

In this tutorial I will show how we can for the two conditions as a result of back data that is in the intersection of the respective conditions. In this case, the conditions are the row and column and the result is the value at the intersection.

When it comes to the intersection of rows and columns then we have a minimum of two types of tasks
  • One condition - How to show as a result of the row and column headers if you happen to know the intersection
  • Two conditions - How to display the information as a result of the intersection if we know the conditions of the row and column

In the picture below you see the situation on the worksheet 'Sheet1'. In this example, you imagine that you fill out a table containing the names of workers and payment of the cash value of a specific date. Here I show the three workers would not be a problem to find how much we paid money to whom. But what if you have 50 to 100... employees and annual filling tables. Then the problem quickly find which employee we are on a specific date to pay out.


So at a certain date, we perform the payment of money. Our goal is to another worksheet, entering a date to see how much we have to pay that day and one worker. You must be aware that this example can be used in other similar situations where we have two conditions header row and column and seek data or a value that is located at the intersection of rows and columns.

In this case, the conditions are us the date and the name of the workers and we are looking for value paid for each worker to a specific date.

Find the Intersection of the two conditions Row/Column in Excel
On the second worksheet 'Sheet2' there is another table that we use to have a certain search results from table 'Sheet1'. In this table it is sufficient to enter the date and Excel formulas will automatically display which the a worker has made payment of funds for a specific date. This way of creating one good formula is that the formula can be copied to the right and down and the formula will return the correct results since we have multiple columns that are a requirement.

You notice that our conditions is Unique (dates and names of employees).

Display data or the value at the intersection of row and column headers if the conditions of work and column
INDEX function has two syntax:

=INDEX(array;row_num;column_num)
=INDEX(reference;row_num;column_num;area_num)


The formula in cell "B2" is as follows. This formula you copy it to the right until the last column and all the way down to the last row. The formula put in one line.

=IFERROR(INDEX(INDIRECT("Sheet1!"&ADDRESS(2;COLUMN(B1)+COLUMN(A1);1)&":$K$50");MATCH($A2;INDIRECT("Sheet1!"&ADDRESS(2;COLUMN(A1)+COLUMN(A1);1)&":"&ADDRESS(50;COLUMN(A1)+COLUMN(A1);1));0);1);"")

The formula above is composed of multiple nested functions. I'm here to try to parse the formula to the parts that you showed how the formula works.

The basic formula is the INDEX/MATCH. It consists of nested functions and how the INDEX-MATCH formulas refer to the respective link where you have detailed explanations. This combination of the two functions often substitutes VLOOKUP formula.

I used the first type of variant syntax. As an array argument was used the INDIRECT function which in combination with ADDRESS function returns a range of cells in which we seek a result.

array => INDIRECT("Sheet1!"&ADDRESS(2;COLUMN(B1)+COLUMN(A1);1)&":$K$50")

As a second argument INDEX function, I used the MATCH function. Match function returns a row number for a particular condition. Inside, the MATCH formula also I used Indirect formula because we need a range of cells in which we seek condition. The formula put in one line

row_num => 
MATCH($A2;INDIRECT("Sheet1!"&ADDRESS(2;COLUMN(A1)+COLUMN(A1);1)&":"&
ADDRESS(50;COLUMN(A1)+COLUMN(A1);1)
);0)

Also in the formula, you notice COLUMN function, which is important because of the copy to the right. If you look at the organization of data on 'Sheet1', then you may notice that the data we seek as a result is in every second column. The combination of Address/Column functions assures us that when copying to the right, skipping one column or in other words I can say increasing the number of columns when copying a formula to the right. The first formula gives back the range B2:B50, other D2:D50, the third number of column F2:F50, etc ...

Looks like this formula below, which uses the Address/Column functions. If you copy to the right you can see how it changes the cell range.

ADDRESS(2;COLUMN(A1)+COLUMN(A1);1)&":"&ADDRESS(50;COLUMN(A1)+COLUMN(A1);1)

I hope you understand how the formula works in full. And finally, I can further say that I used IFERROR function to ignore the error #N/A which occurs if the conditions.

Find intersection point Row and Column header using the VLOOKUP formula

This problem can be solved with one VLOOKUP formula because it is the date in column 'A' unique. So, set this formula in the cell below the 'B2' and copy it to the right all the way down. The principle of operation is the same formula. Set formula in to one line.

=IFERROR(VLOOKUP($A2;INDIRECT("Sheet1!"&ADDRESS(2;COLUMN(A1)+COLUMN(A1);1)&":$K$50");MATCH(B$1;INDIRECT("Sheet1!"&ADDRESS(1;COLUMN(A1)+COLUMN(A1);1)&":$K$1");0);FALSE);"")