How to Return Value Based on Intersection Headers Row and Column

How to Display Headers Row and Column Based on the Value as Criteria

In a situation where we have a range of data with the names of employees and the months of the year, as the situation in the picture below, and we want to know to whom we paid a certain amount also in which month, then we can to return as a result of the name of the employees and the month of payment, if we put any such a condition, because Excel will find the nearest value in the range of data and display it as a result.

So, in search range or a table of data in Excel for the required value, and to return the nearest lower value and headers in the column and row.

In the picture below you see on worksheet "master" range of cells and certain data, i.e. values and Headers Rows and Columns. These are the basic data, which let us search on worksheet "results", and for a certain condition we want to know which is the closest lower value of required value, as the second result we want to know the name of the person to whom it is paid value and in which month.

NOTE: Here I would note that the Value must be Unique. If values are not unique, the formula will return the first nearest lower detected value, in the first column in which found an identical value. Please, take a carefully look picture below and you notice similarities with the picture above. In both figures below provides information in the a range of cells. However in the image below I formated the Table1 based on the range of data in the image above. These differences between the name "range-table / really Excel Table1", I wrote on tutorial "Table vs Table1 in Excel". In the rest of this tutorial you will see why these cell ranges showing twice. Setting Conditional Formatting, if the condition is on a different worksheet

In the pictures above, you notice that the cell is the Intersection of Row and Column has a green background. On this worksheet I have set Conditional Formatting to highlight specific cell. The formula for Highlight Cells in the Intersection of Columns and Rows is not complicated, but the problem arises when the condition is on the another worksheet. In this case our condition cell located on the second Worksheet "results" in the Cell 'B2'.

In order to highlight sections rows and column working I've defined a Cell name on the worksheet "results". I am named as "nearestValue" (see image below).

So the formula for Conditional Formatting a Worksheet "master" is as follows = nearestValue. On the worksheet "results" we want to show the following results:

• The first nearest lower value of the conditions
• Header of Row
• Header of column

So we want to know which person is the closest amount paid on the basis of the required condition in the given month. So we are looking for an Intersection of a Column and Row in the worksheet "master".

I deliberately created another worksheet to further demonstrated how to use Conditional Formatting on a different worksheet, and also because of differences in formulas created, you will see hereinafter views tutorials. If you carefully reading this text below, you will all understand.

In the picture below you see the worksheet "results" where you can see the following:

• In the cell 'A2' we take the requirement that we ask certain amount of money.
• In the cell 'B2' formula as a result displays the nearest lower value set conditions to 'A2' cell.
• In the cell 'A6' formula as a result displays the header line that is. The name of employees which we paid a monetary value.
• In the cell 'B6' formula as a result displays the column header i.e. the month in which we make a payment of monetary value

Also, in the picture below you will see that I have defined name for the cell 'B2'. I put name 'nearestValue', this is important, because of color background intersection of the row and column in a worksheet "master". It is known that the Conditional Formatting using the formula does not work in Excel if the data is not found in the same worksheet, so it is necessary to Define the Name of the Cell or Range of cells. The formulas for displaying intersection of the row and column, as well as display headers rows and columns

End of this tutorial about the intersection and the header rows and columns created formulas are as follows:

• ARRAY formula in cell "B2" is as follows: (the first formula refers to a range of cells, the second formula refers to Table1)

=MAX(IF(master!\$C\$2:\$H\$11<=A2;master!\$C\$2:\$H\$11))

=MAX(IF(Table1[[I]:[VI]]<=A2;Table1[[I]:[VI]]))

• ARRAY formula in cell "A6" is as follows: (the first formula refers to a range of cells, the second formula refers to Table1)

=INDEX(master!\$B\$2:\$B\$11;MIN(IF(master!\$C\$2:\$H\$11=B2;ROW(master!\$C\$2:\$H\$11)-1)))

=INDEX(Table1[Name];MIN(IF(Table1[[I]:[VI]]=B2;ROW(Table1[[I]:[VI]])-1)))

You notice in the formulas above, marked in red number -1, it indicates how many columns located before the first column with the data in the formula or [Name] in the second formula.

• ARRAY formula in cell "B6" is as follows: (the first formula refers to a range of cells, the second formula refers to Table1)

=INDEX(master!\$C\$1:\$H\$1;MIN(IF(master!\$C\$2:\$H\$11=B2;COLUMN(master!\$C\$1:\$H\$1)-2)))