Formula: Help me make a forumla return the X and Y axis when it has a value

[Nicholaes]

So my issue is this, my work made a graph that shows changes in a handbook given a certain section and they marked an X where they changed. So I need a formula that gives me the lesson on the X axis and the lesson on the Y axis when there is a mark filled out where they meet.

example.PNG


So in this photo, I need a formula that when they is an X on the blue mark, it returns the red marks to me.

Is this possible? Thanks!

[GeoffW283]

From your table there are 39 Module-1 sections and 19 "Focus"s yielding 39 * 19 = 741 squares in which you may or may not have an "X".
Worksheet "Table" in the attached workbook is a semblance of your table per your attachment.

On a separate worksheet "Linear" I have converted (automatically by formula) your 2D 39 x 19 table to a linear list of 741 rows and then, also by formula extracted the X's from your table into the appropriate row of my linear list.

From here you can simply filter col-E for X's only. Columns C and D then contain the Focus and Module-section corresponding to each "X" which I think is the result you asked for (your red circled items).

To use the attached workbook populate the TABLE worksheet with your real data (my X's are for testing purposes only). If you have additional Focus's or Module sections you will need to update the Linear worksheet columns A and B as necessary. All other columns are calculated automatically.

=== Details
On worksheet "Linear", the formulas in columns C and D generate all combinations of the user provided columns A (focus) and B (module section).
In C2 and copied down:

Please Login or Register  to view this content.

D2=1 then in D3 and copied down:

Please Login or Register  to view this content.

Column-E is then just an index/match into the 2D "Table" worksheet:
In E2 and copied down:

Please Login or Register  to view this content.

T() suppresses the 0's where there is no "X" in the 2D table.

The attached workbook implements the above.

Hopefully this is close to what you are looking for. Let me know.