Index Match chooses with multiple criteria, pulls from multiple arrays

patrickjpatten

New Member
Joined
Jan 31, 2022
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJKLMNOPQRSTUVWXY
1MarketMar-22Apr-22May-22Jun-22Jul-22Aug-22Sep-22Oct-22Nov-22Dec-22Jan-23Feb-23Mar-23Apr-23May-23Jun-23Jul-23Aug-23Sep-23Oct-23
2
3FinTCOPrice
4
5Fin/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
6Phys/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
7Fin/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
8Phys/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
11Phys
12FinCG-MainlineEastern Gas-SouthLeidy-TranscoSonat-Z0 SouthTCO
13MonthPhysMonthFinMonthPhysMonthFinMonthPhysMonthFinMonthPhysMonthFinMonthPhysMonthFin
14Feb-22-0.715Feb-22-0.205Feb-22-1.395Feb-22-0.578Feb-22-0.881Feb-22-0.505Feb-22-0.5Feb-22-0.005Feb-22-1.065Feb-22-0.468
15Mar-22-0.21Mar-22-0.22Mar-22-0.62Mar-22-0.62Mar-22-0.643Mar-22-0.643Mar-22-0.025Mar-22-0.025Mar-22-0.43Mar-22-0.448
16Apr-22-0.25Apr-22-0.263Apr-22-0.708Apr-22-0.708Apr-22-0.755Apr-22-0.755Apr-22-0.023Apr-22-0.023Apr-22-0.44Apr-22-0.465
17May-22-0.25May-22-0.27May-22-0.883May-22-0.883May-22-0.9May-22-0.9May-22-0.013May-22-0.013May-22-0.52May-22-0.565
18Jun-22-0.26Jun-22-0.27Jun-22-0.93Jun-22-0.93Jun-22-0.973Jun-22-0.973Jun-22-0.033Jun-22-0.033Jun-22-0.6Jun-22-0.628
19Jul-22-0.265Jul-22-0.27Jul-22-0.91Jul-22-0.91Jul-22-0.928Jul-22-0.928Jul-22-0.025Jul-22-0.025Jul-22-0.74Jul-22-0.723
20Aug-22-0.25Aug-22-0.248Aug-22-0.985Aug-22-0.985Aug-22-0.998Aug-22-0.998Aug-22-0.025Aug-22-0.025Aug-22-0.76Aug-22-0.755
21Sep-22-0.318Sep-22-0.325Sep-22-1.528Sep-22-1.528Sep-22-1.533Sep-22-1.533Sep-22-0.025Sep-22-0.025Sep-22-1.05Sep-22-1.035
22Oct-22-0.42Oct-22-0.4Oct-22-1.583Oct-22-1.583Oct-22-1.585Oct-22-1.585Oct-22-0.018Oct-22-0.018Oct-22-1.15Oct-22-1.133
23Nov-22-0.28Nov-22-0.273Nov-22-1.04Nov-22-1.04Nov-22-1.028Nov-22-1.028Nov-22-0.028Nov-22-0.028Nov-22-0.78Nov-22-0.778
24Dec-22-0.2Dec-22-0.218Dec-22-0.855Dec-22-0.855Dec-22-0.848Dec-22-0.848Dec-22-0.02Dec-22-0.02Dec-22-0.605Dec-22-0.613
25Jan-23-0.18Jan-23-0.17Jan-23-0.645Jan-23-0.645Jan-23-0.613Jan-23-0.613Jan-23-0.015Jan-23-0.015Jan-23-0.39Jan-23-0.385
26Feb-23-0.16Feb-23-0.155Feb-23-0.603Feb-23-0.603Feb-23-0.598Feb-23-0.598Feb-23-0.02Feb-23-0.02Feb-23-0.4Feb-23-0.39
MC1
Cells with Data Validation
CellAllowCriteria
A3List=$A$11:$A$12
C3:D3List=$F$12:$Y$12
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,748
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Aug 18, 2015
Messages
12,496
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
ABCDEFGHIJKLMNOPQ
1Market3/1/20224/1/20225/1/20226/1/20227/1/20228/1/20229/1/202210/1/202211/1/202212/1/20221/1/20232/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
3FinTCOPrice
Sheet13
Cell Formulas
RangeFormula
F2:Q2F2=VLOOKUP(F1:Q1,$F$14:$Y$26,MATCH($C$3,$F$12:$Y$12,0)+1+2*($A$3="Fin"),0)
Dynamic array formulas.
 
Solution

patrickjpatten

New Member
Joined
Jan 31, 2022
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,176,042
Messages
5,901,092
Members
434,870
Latest member
michud08

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