Extract specific tables from a set of large tables

[aditya_7]

Hello All,

I have a spreadsheet that looks like the below. This is a dummy sheet and the actual file I'm working on has large no. of data. My goal here is to be able to extract only the tables I specify on to a new excel. For eg: In a new excel file, I should only be required to type 'Comp B' and a formula should be able to return the table from B10 to G13. Manually copy pasting is not possible as there are lot of tables in sheet.

Comps.xlsx
	A	B	C	D	E	F	G
1
2
3		Name	Start Date	EMV	BMV	gross	net
4		Comp A	06-01-2008	78,585.00	71,278.00	6.25	5.25
5		a		17,359.00	23,001.00	3	2
6		b		24,204.00	19,466.00	9	8
7		c		24,897.00	13,198.00	3	2
8		d		12,125.00	15,613.00	10	9
9
10		Comp B	06-02-2008	2,24,523.00	2,35,008.00	5	4
11		p		70,058.00	55,578.00	4	3
12		q		75,337.00	99,719.00	2	1
13		r		79,128.00	79,711.00	9	8
14
15		Comp B	06-03-2008	6,87,804.00	6,92,370.00	3.67	2.67
16		x		2,18,520.00	2,46,680.00	2	1
17		y		2,21,401.00	2,15,591.00	4	3
18		z		2,47,883.00	2,30,099.00	5	4
Sheet1

 

[Jeffrey Mahoney]

Please try this:

In my example cell K3 is the trigger cell. Name the Range CompTable

Book6.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
3	Name	Start Date	EMV	BMV	gross	net				Comp A
4	Comp A	1/6/2008	78585	71278	6.25	5.25				Name	Start Date	EMV	BMV	gross	net
5	a		17359	23001	3	2				Comp A	39453	78585	71278	6.25	5.25
6	b		24204	19466	9	8				a	0	17359	23001	3	2
7	c		24897	13198	3	2				b	0	24204	19466	9	8
8	d		12125	15613	10	9				c	0	24897	13198	3	2
9										d	0	12125	15613	10	9
10	Comp B	2/6/2008	224523	235008	5	4
11	p		70058	55578	4	3
12	q		75337	99719	2	1
13	r		79128	79711	9	8
14
15	Comp C	3/6/2008	687804	692370	3.6666667	2.6666667
16	x		218520	246680	2	1
17	y		221401	215591	4	3
18	z		247883	230099	5	4
Sheet3

Cell Formulas
Range	Formula
K5:P9	K5	=LET(One,MATCH(K$3,INDEX(CompTable,,1),0),TL,INDEX(CompTable,1,1),Two,MATCH(TRUE,ISBLANK(OFFSET(TL,One-1,0,100,1)),0),OFFSET(TL,One-1,0,Two-1,6))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
CompTable	=Sheet3!$B$4:$G$24	K5

 

[Jeffrey Mahoney]

You will, of course, have to create a named range in your other workbook leading to the range with all those little tables. The formula should still work

 

[aditya_7]

You're a genius! thanks!

If I understood correctly, you're using INDEX & MATCH function within the LET function. Can this be also done using a single XLOOKUP ? So basically instead of using INDEX & MATCH we use only XLOOKUP? I'm more familiar with XLOOKUP so would be easier to wrap my brain around all this

 

[Jeffrey Mahoney]

You can use XLookup within the LET function. Any function you can dream of gets applied to the variable

 

[Jeffrey Mahoney]

If you think that was the right answer, please mark it as such.

 

[aditya_7]

Yes sure, just one request. Can you please replace INDEX MATCH with XLOOKUP? I tried but couldn't do it

 

[Jeffrey Mahoney]

Sorry, xlookup can only return a single cell or value. I thought you were asking for future uses of LET. Offset is another function that can return a range. Index is better because I used it to return a single column to help find the spaces. Match is used to find the Comp B and spaces.

 

[aditya_7]

Thank you very much ! This is now marked as solution