Struggling With Creating INDEX/MATCH Formula

[simonj64]

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

 

[simonj64]

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

 

[Andrew Poulsom]

Formula in J2 copied down and across:

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Key	Jan	Feb	Mar	Apr	May	Jun		Key	Jan	Feb	Mar	Apr	May	Jun
2	Set 1	5	10	15	20	25	30		Set 1	5	10	15	20	25	30
3	Set 2	6	11	16	21	26	31		Set 2	6	11	16	21	26	31
4	Set 3	8	13	18	23	28	33		Set 3	8	13	18	23	28	33
5	Set 4	11	16	21	26	31	36		Set 4	11	16	21	26	31	36
6	Set 5	16	21	26	31	36	41		Set 5	16	21	26	31	36	41
Sheet1

Cell Formulas
Range	Formula
J2	=INDEX($B$2:$G$6,MATCH($I2,$A$2:$A$6,FALSE),MATCH(J$1,$B$1:$G$1,FALSE))

 

[simonj64]

Hi Andrew - thank you very much, this has worked brilliantly !

 

[simonj64]

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

 

[Andrew Poulsom]

Like this?

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Key1	Key2	Jan	Feb	Mar	Apr	May	Jun		Key1	Key2	Jan	Feb	Mar	Apr	May	Jun
2	Set 1	1	5	10	15	20	25	30		Set 1	1	5	10	15	20	25	30
3	Set 1	2	6	11	16	21	26	31		Set 1	2	6	11	16	21	26	31
4	Set 1	3	8	13	18	23	28	33		Set 1	3	8	13	18	23	28	33
5	Set 1	4	11	16	21	26	31	36		Set 1	4	11	16	21	26	31	36
6	Set 1	5	16	21	26	31	36	41		Set 1	5	16	21	26	31	36	41
Sheet1

Cell Formulas
Range	Formula
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))

 

[simonj64]

Thanks Andrew - this is working perfectly - I'll take a copy for my records. Regards Simonj