Index Match chooses with multiple criteria, pulls from multiple arrays

[patrickjpatten]

Thought I was pretty good at index match but my mind is frayed and I can't seem to find this on google.

I am trying to populate a cells F3:Q3 with an array found somewhere from F12:Y26

The user chooses criteria in A3 and C3 and then the months in Row 1 choose the correct month.
Find the correct array using A3&C3 then find the correct month using Row 1
All help appreciated. Thank you in advance

ExcelQuestion.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1	Market					Mar-22	Apr-22	May-22	Jun-22	Jul-22	Aug-22	Sep-22	Oct-22	Nov-22	Dec-22	Jan-23	Feb-23	Mar-23	Apr-23	May-23	Jun-23	Jul-23	Aug-23	Sep-23	Oct-23
2
3	Fin		TCO		Price
4
5					Fin/TCO	-0.468	-0.448	-0.465	-0.565	-0.628	-0.723	-0.755	-1.035	-1.1325	-0.778	-0.613	-0.385
6					Phys/TCO	-1.065	-0.43	-0.44	-0.52	-0.6	-0.74	-0.76	-1.05	-1.15	-0.78	-0.605	-0.39
7					Fin/CG-Mainline	-0.205	-0.22	-0.2625	-0.27	-0.27	-0.27	-0.248	-0.325	-0.4	-0.273	-0.218	-0.17
8					Phys/CG-Main	-0.715	-0.21	-0.25	-0.25	-0.26	-0.265	-0.25	-0.318	-0.42	-0.28	-0.2	-0.18
9
10
11	Phys
12	Fin					CG-Mainline				Eastern Gas-South				Leidy-Transco				Sonat-Z0 South				TCO
13						Month	Phys	Month	Fin	Month	Phys	Month	Fin	Month	Phys	Month	Fin	Month	Phys	Month	Fin	Month	Phys	Month	Fin
14						Feb-22	-0.715	Feb-22	-0.205	Feb-22	-1.395	Feb-22	-0.578	Feb-22	-0.881	Feb-22	-0.505	Feb-22	-0.5	Feb-22	-0.005	Feb-22	-1.065	Feb-22	-0.468
15						Mar-22	-0.21	Mar-22	-0.22	Mar-22	-0.62	Mar-22	-0.62	Mar-22	-0.643	Mar-22	-0.643	Mar-22	-0.025	Mar-22	-0.025	Mar-22	-0.43	Mar-22	-0.448
16						Apr-22	-0.25	Apr-22	-0.263	Apr-22	-0.708	Apr-22	-0.708	Apr-22	-0.755	Apr-22	-0.755	Apr-22	-0.023	Apr-22	-0.023	Apr-22	-0.44	Apr-22	-0.465
17						May-22	-0.25	May-22	-0.27	May-22	-0.883	May-22	-0.883	May-22	-0.9	May-22	-0.9	May-22	-0.013	May-22	-0.013	May-22	-0.52	May-22	-0.565
18						Jun-22	-0.26	Jun-22	-0.27	Jun-22	-0.93	Jun-22	-0.93	Jun-22	-0.973	Jun-22	-0.973	Jun-22	-0.033	Jun-22	-0.033	Jun-22	-0.6	Jun-22	-0.628
19						Jul-22	-0.265	Jul-22	-0.27	Jul-22	-0.91	Jul-22	-0.91	Jul-22	-0.928	Jul-22	-0.928	Jul-22	-0.025	Jul-22	-0.025	Jul-22	-0.74	Jul-22	-0.723
20						Aug-22	-0.25	Aug-22	-0.248	Aug-22	-0.985	Aug-22	-0.985	Aug-22	-0.998	Aug-22	-0.998	Aug-22	-0.025	Aug-22	-0.025	Aug-22	-0.76	Aug-22	-0.755
21						Sep-22	-0.318	Sep-22	-0.325	Sep-22	-1.528	Sep-22	-1.528	Sep-22	-1.533	Sep-22	-1.533	Sep-22	-0.025	Sep-22	-0.025	Sep-22	-1.05	Sep-22	-1.035
22						Oct-22	-0.42	Oct-22	-0.4	Oct-22	-1.583	Oct-22	-1.583	Oct-22	-1.585	Oct-22	-1.585	Oct-22	-0.018	Oct-22	-0.018	Oct-22	-1.15	Oct-22	-1.133
23						Nov-22	-0.28	Nov-22	-0.273	Nov-22	-1.04	Nov-22	-1.04	Nov-22	-1.028	Nov-22	-1.028	Nov-22	-0.028	Nov-22	-0.028	Nov-22	-0.78	Nov-22	-0.778
24						Dec-22	-0.2	Dec-22	-0.218	Dec-22	-0.855	Dec-22	-0.855	Dec-22	-0.848	Dec-22	-0.848	Dec-22	-0.02	Dec-22	-0.02	Dec-22	-0.605	Dec-22	-0.613
25						Jan-23	-0.18	Jan-23	-0.17	Jan-23	-0.645	Jan-23	-0.645	Jan-23	-0.613	Jan-23	-0.613	Jan-23	-0.015	Jan-23	-0.015	Jan-23	-0.39	Jan-23	-0.385
26						Feb-23	-0.16	Feb-23	-0.155	Feb-23	-0.603	Feb-23	-0.603	Feb-23	-0.598	Feb-23	-0.598	Feb-23	-0.02	Feb-23	-0.02	Feb-23	-0.4	Feb-23	-0.39
MC1

Cells with Data Validation
Cell	Allow	Criteria
A3	List	=$A$11:$A$12
C3:D3	List	=$F$12:$Y$12

 

[steve the fish]

You would find it much easier if you repeated F12 into G12:I12 and J12 into K12:M12 etc. However the way its setup you could do this:

=INDEX(($F$12:$I$26,$J$12:$M$26,$N$12:$Q$26,$R$12:$U$26,$V$12:$Y$26),MATCH(F1,$F$12:$F$26,0),MATCH($A$3,$F$13:$I$13,0),(MATCH($C$3,$F$12:$Y$12,0)+3)/4)

 

[Eric W]

Are all the dates in each row of the table the same? Is each 4-column section in the table laid out the same? If so, try:

Book1 (version 1).xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Market					3/1/2022	4/1/2022	5/1/2022	6/1/2022	7/1/2022	8/1/2022	9/1/2022	10/1/2022	11/1/2022	12/1/2022	1/1/2023	2/1/2023
2						-0.4475	-0.465	-0.565	-0.6275	-0.7225	-0.755	-1.035	-1.1325	-0.7775	-0.6125	-0.385	-0.39
3	Fin		TCO		Price
Sheet13

Cell Formulas
Range	Formula
F2:Q2	F2	=VLOOKUP(F1:Q1,$F$14:$Y$26,MATCH($C$3,$F$12:$Y$12,0)+1+2*($A$3="Fin"),0)
Dynamic array formulas.

 

[patrickjpatten]

Thanks to both Eric and Steve - both good answers, Eric I think you got the just of what I'm asking the most. Thank you for taking time outta your day to sort this for me.

 

[Eric W]

Glad we could help! Thanks for the feedback.