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:

[TABLE="width: 704"]
<tbody>[TR]
[TD="class: xl63, width: 64"]y=2x+1[/TD]
[TD="class: xl63, width: 64, align: right"]1[/TD]
[TD="class: xl63, width: 64, align: right"]2[/TD]
[TD="class: xl63, width: 64, align: right"]3[/TD]
[TD="class: xl63, width: 64, align: right"]4[/TD]
[TD="class: xl63, width: 64, align: right"]5[/TD]
[TD="class: xl63, width: 64, align: right"]6[/TD]
[TD="class: xl63, width: 64, align: right"]7[/TD]
[TD="class: xl63, width: 64, align: right"]8[/TD]
[TD="class: xl63, width: 64, align: right"]9[/TD]
[TD="class: xl63, width: 64, align: right"]10[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]21[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]22[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]3[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]23[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]4[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]24[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]5[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]25[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]6[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]26[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]7[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]27[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]8[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]28[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]9[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]29[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]10[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]24[/TD]
[TD="align: right"]26[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]30[/TD]
[/TR]
</tbody>[/TABLE]

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