# Retrieve X and Y value from table

#### bobpijnenburg

##### New Member
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?

Kind regards,

Bob

### Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the \$ sign).
Hello,

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

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

Code:
``````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?

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

Replies
11
Views
466
Replies
1
Views
134
Replies
4
Views
150
Replies
25
Views
477
Replies
10
Views
323

1,203,232
Messages
6,054,271
Members
444,713
Latest member
SAK

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back