Pulling Data from Table

[TBone32]

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!


[TABLE="width: 196"]
<colgroup><col width="49" span="4" style="width:37pt"> </colgroup><tbody>[TR]
[TD="class: xl70, width: 49"]1[/TD]
[TD="class: xl70, width: 49"]12[/TD]
[TD="class: xl70, width: 49"]8[/TD]
[TD="class: xl70, width: 49"]6[/TD]
[/TR]
[TR]
[TD="class: xl70"]2[/TD]
[TD="class: xl70"]22[/TD]
[TD="class: xl70"]10[/TD]
[TD="class: xl70"]5[/TD]
[/TR]
[TR]
[TD="class: xl70"]9[/TD]
[TD="class: xl70"]20[/TD]
[TD="class: xl70"]4[/TD]
[TD="class: xl70"]23[/TD]
[/TR]
[TR]
[TD="class: xl70"]13[/TD]
[TD="class: xl70"]7[/TD]
[TD="class: xl70"]3[/TD]
[TD="class: xl70"]21[/TD]
[/TR]
[TR]
[TD="class: xl70"]11[/TD]
[TD="class: xl70"]24[/TD]
[TD="class: xl70"]14[/TD]
[TD="class: xl70"]19[/TD]
[/TR]
[TR]
[TD="class: xl70"]15[/TD]
[TD="class: xl70"]18[/TD]
[TD="class: xl70"]16[/TD]
[TD="class: xl70"]17[/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

Maybe something like this...


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[TD="bgcolor: #DCE6F1"]

G​

[/TD]
[TD="bgcolor: #DCE6F1"]

H​

[/TD]
[TD="bgcolor: #DCE6F1"]

I​

[/TD]
[TD="bgcolor: #DCE6F1"]

J​

[/TD]
[TD="bgcolor: #DCE6F1"]

K​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD][/TD]
[TD]

1​

[/TD]
[TD]

12​

[/TD]
[TD]

8​

[/TD]
[TD]

6​

[/TD]
[TD][/TD]
[TD]

Search​

[/TD]
[TD][/TD]
[TD]

Result1​

[/TD]
[TD]

Result2​

[/TD]
[TD]

Result3​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD][/TD]
[TD]

2​

[/TD]
[TD]

22​

[/TD]
[TD]

10​

[/TD]
[TD]

5​

[/TD]
[TD][/TD]
[TD]

4​

[/TD]
[TD][/TD]
[TD]

9​

[/TD]
[TD]

20​

[/TD]
[TD]

23​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD][/TD]
[TD]

9​

[/TD]
[TD]

20​

[/TD]
[TD]

4​

[/TD]
[TD]

23​

[/TD]
[TD][/TD]
[TD]

RangeRow​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD][/TD]
[TD]

13​

[/TD]
[TD]

7​

[/TD]
[TD]

3​

[/TD]
[TD]

21​

[/TD]
[TD][/TD]
[TD]

3​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD][/TD]
[TD]

11​

[/TD]
[TD]

24​

[/TD]
[TD]

14​

[/TD]
[TD]

19​

[/TD]
[TD][/TD]
[TD]

RangeColumn​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD][/TD]
[TD]

15​

[/TD]
[TD]

18​

[/TD]
[TD]

16​

[/TD]
[TD]

17​

[/TD]
[TD][/TD]
[TD]

3​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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]

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.


​

 

[TBone32]

This worked great, thank you.

 

[TBone32]

Sorry for the duplicate.