# Subset rows and columns of excel table using structured reference, extract data into another table

#### LetsMeasure

##### New Member
Suppose I have two excel tables, where TableSmall is a subset of TableBig (TableBig has rows R1-R1) and columns C1-C5. They share some ID column. TableSmall has an arbitrary subset of rows and columns, e.g., rows R5, R6, and R10, columns C3 and C5).

I would like to extract data from TableBig into TableSmall. I can do this using index/match, where I have used helper cells to look up the matching rows an columns, and then get the values in TableBig by using INDEX(TableBig, row I looked up, column I looked up). This approach is shown below.

However, I think there must be a way to do this that doesn't involve helper cells and is probably more elegant. A previous post (How do I refer to the header of this Table Column?) got part of the way there, but it wasn't quite the same question -- and I am having trouble making that example work.

Thanks in advance for any help, and please accept my apologies if I missed a more relevant post.

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

#### Fluff

##### MrExcel MVP, Moderator
Excel Formula:
``=INDEX(TableBig,MATCH([@ID],TableBig[ID],0),MATCH(TableSmall[[#Headers],[C1]],TableBig[#Headers],0))``

#### LetsMeasure

##### New Member
Excel Formula:
``=INDEX(TableBig,MATCH([@ID],TableBig[ID],0),MATCH(TableSmall[[#Headers],[C1]],TableBig[#Headers],0))``
That is simple and solves the problem. Thank you very much!

For my edification, is there a way to do it without MATCH? I.e., since I have shared row and column names, is there any way to refer to TableBig directly? E.g., something along the (conceptual) lines of the following?
TableBig[@ID, C1]

#### Fluff

##### MrExcel MVP, Moderator
You could use vlookup or xlookup rather than index/match/match.

#### LetsMeasure

##### New Member
Got it. Thanks again for the helpful and quick replies!

#### Fluff

##### MrExcel MVP, Moderator
You're welcome & thanks for the feedback.

Replies
5
Views
60
Replies
8
Views
537
Replies
1
Views
188
Replies
1
Views
127
Replies
1
Views
121

1,147,735
Messages
5,742,866
Members
423,760
Latest member
photogfrog

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