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

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
How about
Excel Formula:
=INDEX(TableBig,MATCH([@ID],TableBig[ID],0),MATCH(TableSmall[[#Headers],[C1]],TableBig[#Headers],0))
 
Upvote 0
Solution
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]
 
Upvote 0
You could use vlookup or xlookup rather than index/match/match.
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,223,952
Messages
6,175,594
Members
452,656
Latest member
earth

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
Back
Top