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

LetsMeasure

New Member
Joined
Jul 24, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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.

Screen Shot 07-24-21 at 03.49 PM.PNG
 

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
Joined
Jun 12, 2014
Messages
65,728
Office Version
  1. 365
Platform
  1. Windows
How about
Excel Formula:
=INDEX(TableBig,MATCH([@ID],TableBig[ID],0),MATCH(TableSmall[[#Headers],[C1]],TableBig[#Headers],0))
 
Solution

LetsMeasure

New Member
Joined
Jul 24, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
How about
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
Joined
Jun 12, 2014
Messages
65,728
Office Version
  1. 365
Platform
  1. Windows
You could use vlookup or xlookup rather than index/match/match.
 

LetsMeasure

New Member
Joined
Jul 24, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Got it. Thanks again for the helpful and quick replies!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,728
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top