Struggling With Creating INDEX/MATCH Formula

simonj64

Board Regular
Joined
Apr 1, 2006
Messages
86
I am trying to create an INDEX MATCH function which needs both a key and calendar values to lookup a value in another worksheet. SO below is the data and in the sheet to have the formula, I need to know what the value is in the Lookup table for Set2 and Mar.

Can someone advise on how the INDEX MATCH function should be constructed ?

Lookup Table
Key Jan Feb Mar Apr May Jun
Set 1 5 10 15 20 25 30
Set 2 6 11 16 21 26 31
Set 3 8 13 18 23 28 33
Set 4 11 16 21 26 31 36
Set 5 16 21 26 31 36 41


Key Jan Feb Mar Apr May Jun
Set 1
Set 2 ?
Set 3
Set 4
Set 5
 
I am trying to create an INDEX MATCH function which needs both a key and calendar values to lookup a value in another worksheet. SO below is the data and in the sheet to have the formula, I need to know what the value is in the Lookup table for Set2 and Mar.

Can someone advise on how the INDEX MATCH function should be constructed ?

Lookup Table
Key Jan Feb Mar Apr May Jun
Set 1 5 10 15 20 25 30
Set 2 6 11 16 21 26 31
Set 3 8 13 18 23 28 33
Set 4 11 16 21 26 31 36
Set 5 16 21 26 31 36 41


Key Jan Feb Mar Apr May Jun
Set 1
Set 2 ?
Set 3
Set 4
Set 5



Apologies, that the format of the table is not too good - just to add, the formula I need to construct needs to go in all cells (not just Set 2 /Mar to lookup the corresponding values in the Lookup table

Thanks

Simonj
 
Upvote 0
Formula in J2 copied down and across:


Excel 2010
ABCDEFGHIJKLMNO
1KeyJanFebMarAprMayJunKeyJanFebMarAprMayJun
2Set 151015202530Set 151015202530
3Set 261116212631Set 261116212631
4Set 381318232833Set 381318232833
5Set 4111621263136Set 4111621263136
6Set 5162126313641Set 5162126313641
Sheet1
Cell Formulas
RangeFormula
J2=INDEX($B$2:$G$6,MATCH($I2,$A$2:$A$6,FALSE),MATCH(J$1,$B$1:$G$1,FALSE))
 
Upvote 0
Hi Andrew,

Just one further query - If I now have 2 keys in cols B and J and assuming we are looking at the the cell (instead of J2 it moves to K2), how would you join the second Set columns together ? I assume the two matches in the formula relate to the horizontal and vertical searches - so if we now have 2 horizontal searches, what is the syntax for the formula. I have tried adding a 3rd Match construct in the formula but for some reason it is not liking it.

Thanks in advance

Simonj
 
Upvote 0
Like this?


Excel 2010
ABCDEFGHIJKLMNOPQ
1Key1Key2JanFebMarAprMayJunKey1Key2JanFebMarAprMayJun
2Set 1151015202530Set 1151015202530
3Set 1261116212631Set 1261116212631
4Set 1381318232833Set 1381318232833
5Set 14111621263136Set 14111621263136
6Set 15162126313641Set 15162126313641
Sheet1
Cell Formulas
RangeFormula
L2=INDEX($C$2:$H$6,MATCH(1,INDEX(($A$2:$A$6=$J2)*($B$2:$B$6=$K2),),FALSE),MATCH(L$1,$C$1:$H$1,FALSE))
 
Upvote 0

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