Retrieve X and Y value from table

[bobpijnenburg]

Dear all,

I have a question if there is a function in excel that can help me out. I try to explain my situation as clear as possible.

I have to retrieve the values of the X and Y component of a table where the value is equal to a specific value. I give an explanation:

y=2x+1	1	2	3	4	5	6	7	8	9	10
1	3	5	7	9	11	13	15	17	19	21
2	4	6	8	10	12	14	16	18	20	22
3	5	7	9	11	13	15	17	19	21	23
4	6	8	10	12	14	16	18	20	22	24
5	7	9	11	13	15	17	19	21	23	25
6	8	10	12	14	16	18	20	22	24	26
7	9	11	13	15	17	19	21	23	25	27
8	10	12	14	16	18	20	22	24	26	28
9	11	13	15	17	19	21	23	25	27	29
10	12	14	16	18	20	22	24	26	28	30

<tbody>
</tbody>

From this table give the x and the y component of the values that are equal to 13, and that, if possible, in a neat list. Is that possible?

Thanks in advance for your time,

Kind regards,

Bob

 

[onlyadrafter]

Hello,

Assuming y=2x+1 is in cell A1.

If you enter 13 into cell A13, then run the macro

Sub FIND_COMPONENTS()
    For MY_COL = 2 To Cells(2, Columns.Count).End(xlToLeft).Column
        For MY_ROW = 2 To Range("K" & Rows.Count).End(xlUp).Row
            If Cells(MY_ROW, MY_COL).Value = Range("A13").Value Then
                Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = _
                    "X = " & Cells(1, MY_COL).Value & "; Y = " & Cells(MY_ROW, 1).Value
            End If
        Next MY_ROW
    Next MY_COL
End Sub

You haven't specified what you want to do with the data after it has been found.

Is this what you expect?

 

[bobpijnenburg]

Hi onlyadrafter,

Thank you very much, exactly what I needed! Hoped that this might be a standard function in Excel, but this works perfectly.

Have a nice day and thanks for your time.

Kind regards,

Bob