# Pulling Data from Table

#### TBone32

##### New Member
I would like help retrieving data from a table such as below. I would like to be able to search for a number and retrieve the 3 values in the same line. Example: Search for 4 and the results would be 9, 20, & 23. Thank you!

 1 12 8 6 2 22 10 5 9 20 4 23 13 7 3 21 11 24 14 19 15 18 16 17

<colgroup><col width="49" span="4" style="width:37pt"> </colgroup><tbody>
</tbody>

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### Marcelo Branco

##### MrExcel MVP
Maybe something like this...

 A​ B​ C​ D​ E​ F​ G​ H​ I​ J​ K​ 1​ 2​ 1​ 12​ 8​ 6​ Search​ Result1​ Result2​ Result3​ 3​ 2​ 22​ 10​ 5​ 4​ 9​ 20​ 23​ 4​ 9​ 20​ 4​ 23​ RangeRow​ 5​ 13​ 7​ 3​ 21​ 3​ 6​ 11​ 24​ 14​ 19​ RangeColumn​ 7​ 15​ 18​ 16​ 17​ 3​ 8​

<tbody>
</tbody>

Array formula in G5
=SMALL(IF(B2:E7=G3,ROW(B2:E7)-ROW(B2)+1),1)
confirmed with Ctrl+Shift+Enter, not just Enter

Array formula in G7
=SMALL(IF(B2:E7=G3,COLUMN(B2:E7)-COLUMN(B2)+1),1)
confirmed with Ctrl+Shift+Enter, not just Enter

Array formula in I3 copied across until K3
=INDEX(\$B\$2:\$E\$7,\$G\$5,SMALL(IF(ROW(\$B\$2:\$E\$7)-ROW(\$B\$2)+1=\$G\$5,IF(COLUMN(\$B\$2:\$E\$7)-COLUMN(\$B\$2)+1<>\$G\$7,COLUMN(\$B\$2:\$E\$7)-COLUMN(\$B\$2)+1)),COLUMNS(\$I3:I3)))
confirmed with Ctrl+Shift+Enter, not just Enter

Hope this helps

M.

#### My Aswer Is This

##### Well-known Member
When I see questions like this and this is said:

Pulling Data from Table

How do we know where the Table is unless it's the only Table on the sheet.

Or we are given the name of the Table.

Table and sheet are not the same.

Last edited:

#### TBone32

##### New Member
This worked great, thank you.

#### TBone32

##### New Member
Sorry for the duplicate.

Replies
4
Views
784
Replies
3
Views
95
Replies
7
Views
125
Replies
6
Views
158
Replies
1
Views
65

1,127,444
Messages
5,624,811
Members
416,056
Latest member
VARSHA V VASWANI

### 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