CHOOSE function with multiple tables and multiple columns

[nbowland]

I am trying to complete a form that extracts information from multiple tables with several columns. The column index is rarely the same, so I can't just hard code a column index number. I tried using the INDEX with MATCH functions to replace the column index number, but #VALUE error appeared. Does anyone know how to extract from multiple tables with several columns? Thank you.

-Nick

 

[Jonmo1]

The column index is rarely the same, so I can't just hard code a column index number.

Can you post the formula you would use if that wasn't an issue?
i.e. If the columns never changed, and you 'could' hard code the column index #.

 

[nbowland]

Hi Bryan,

Thank you for the response! Below is the most current code I tried...

=CHOOSE(I5,INDEX(G14:L18,MATCH(G2,F14:F18,0),MATCH(H4,G13:L13,0)),INDEX(G22:L26,MATCH(G2,F22:F26,0),MATCH(H4,G21:L21,0)))

I also used this code with the actual Index #. It worked, but I can't use this without hard-coding every cell to pull from a different column...

=VLOOKUP(G2,CHOOSE(D17,F14:AD18,F22:AD26,F30:AD34),3,0)

 

[Jonmo1]

So is each range like a duplicate of each other, as far as the row and column headers go ?
i.e. Is F14:F18 the same as F22:F26 ?
and is G13:L13 the same as G21:L21 ?

 

[Jonmo1]

If the Row and Column headers are the same in each 'section', try
=INDEX(OFFSET($G$14:$L$18,(I5-1)*8,0),MATCH(G2,$F$14:$F$18,0),MATCH(H4,$G$13:$L$13,0))

If the headers are unique in each 'section', try
INDEX(OFFSET($G$14:$L$18,(I5-1)*8,0),MATCH(G2,OFFSET($F$14:$F$18,(I5-1)*8,0),0),MATCH(H4,OFFSET($G$13:$L$13,(I5-1)*8,0),0))

 

[nbowland]

Thank you. I tried the latter formula, as the headers are unique. It didn't quite work though. Is there a way that I could send you the workbook?

 

[Jonmo1]

No we like to keep the conversation in the forum.

You might upload it to a filesharing site like dropbox or something.

Otherwise, try using an HTML tool to post nicely formatted tables from your sheet to the forum
See my signature for a link.

 

[Jonmo1]

Here's an example of it working with Just 2 sections of the tables.

Unknown
	E	F	G	H	I	J	K	L
2	224		H
3
4				9
5					2
6
7
8
9
10
11
12
13			1	2	3	4	5	6
14		A	28	6	93	87	3	50
15		B	22	36	62	97	90	99
16		C	62	32	90	14	5	32
17		D	70	57	35	33	66	100
18		E	74	32	57	83	27	3
19
20
21			7	8	9	10	11	12
22		F	245	217	243	281	235	244
23		G	242	210	240	252	260	299
24		H	277	209	224	243	280	203
25		I	263	234	203	300	223	227
26		J	222	253	255	254	228	212
Sheet1

Cell Formulas
Range	Formula
E2	=INDEX(OFFSET($G$14:$L$18,(I5-1)*8,0),MATCH(G2,OFFSET($F$14:$F$18,(I5-1)*8,0),0),MATCH(H4,OFFSET($G$13:$L$13,(I5-1)*8,0),0))

 

[nbowland]

I really appreciate you help! I need to research the OFFSET function so that I understand exactly how it fits into the formula. If I can't, I will copy / paste a piece of the worksheet for you. Again, your time is greatly appreciated!

 

[Mike Szczesny]

Hi nbowland,
Can you use something like this. I have incorporated the choose function to allow flexibility to select table and match ranges.
Mike Szczesny

Excel 2012

	A	B	C	D	E	F
1	Table #	2
2	Column	x
3	Row	d
4	Number	29
5
6	Table1		x	xx	xxx	xxxx
7		a	1	2	3	4
8		b	5	6	7	8
9		c	9	10	11	12
10		d	13	14	15	16
11
12
13	Table2		x	xx	xxx	xxxx
14		a	17	18	19	20
15		b	21	22	23	24
16		c	25	26	27	28
17		d	29	30	31	32
18
19
20	29

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula B4 =A20 A20 =INDEX(CHOOSE($B$1,$C$7:$F$10,$C$14:$F$17),MATCH(B3,CHOOSE($B$1,$B$7:$B$10,$B$14:$B$17),0),MATCH($B$2,CHOOSE($B$1,$C$6:$F$6,$C$13:$F$13),0)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>