Match and index

[Mike2502]

Hi guys,

I have tried, youtubed etc however still can't manage to figure this out (noob here). When I try index and match it returns as spill/error. I am trying to use B2 and B3 as changeable variables as per below. The length of employment is the column D and as rows will always be either 3,4,5,10 and as shown below it is '5' this should go across the columns F,G,H,I to fetch the data and return value H6 (£12).

I hope this makes sense?

 

[Toadstool]

Hi Mike2502,

Does this do what you want?

Mike2502-Q2.xlsx
	A	B	C	D	E	F	G	H	I
1	LoE	5		LoE	Cost	3	4	5	10
2	Rows	5		1	12	12	12	12	12
3	Result	12		2	22	14	16	18	20
4				3	77	16	20	24	28
5				4	88	18	24	30	36
6				5	99	20	28	12	44
7				6	111	22	32	42	52
8				7	222	24	36	48	60
9				8	305	26	40	54	68
10				9	388	28	44	60	76
11				10	471	30	48	66	84
12				11	554	32	52	72	92
13				12	637	34	56	78	100
Sheet1

Cell Formulas
Range	Formula
B3	B3	=INDEX($F$2:$I$13,MATCH($B$1,$D$2:$D$13,0),MATCH($B$2,$F$1:$I$1,0))

 

[jasonb75]

Try

=INDEX(Table1[[3]:[10]],B1,MATCH(B2,Table1[[#Headers],[3]:[10]],0))

I've assumed Table1 as you didn't tell us the actual name in your post.

 

[Mike2502]

Hi Mike2502,

Does this do what you want?

Mike2502-Q2.xlsx
	A	B	C	D	E	F	G	H	I
1	LoE	5		LoE	Cost	3	4	5	10
2	Rows	5		1	12	12	12	12	12
3	Result	12		2	22	14	16	18	20
4				3	77	16	20	24	28
5				4	88	18	24	30	36
6				5	99	20	28	12	44
7				6	111	22	32	42	52
8				7	222	24	36	48	60
9				8	305	26	40	54	68
10				9	388	28	44	60	76
11				10	471	30	48	66	84
12				11	554	32	52	72	92
13				12	637	34	56	78	100
Sheet1

Cell Formulas
Range	Formula
B3	B3	=INDEX($F$2:$I$13,MATCH($B$1,$D$2:$D$13,0),MATCH($B$2,$F$1:$I$1,0))

Hi Toadstoal,

Thanks for your reply! Unfortunately I get an error on excel #N/A. However, it is what I would like, I'm not sure why the error is occurring?

 

[Mike2502]

Try

=INDEX(Table1[[3]:[10]],B1,MATCH(B2,Table1[[#Headers],[3]:[10]],0))

I've assumed Table1 as you didn't tell us the actual name in your post.

Hi Jason, thanks for the reply.

Cell b1 needs to match data in column D, is this correct? And this also comes up with #N/A error...

 

[Toadstool]

Hi Toadstoal,

Thanks for your reply! Unfortunately I get an error on excel #N/A. However, it is what I would like, I'm not sure why the error is occurring?

My fault, I should have noted you're using a Table. To search the Header I'll also need to change the B2 5 to text, hence the &""

Mike2502-Q2.xlsx
	A	B	C	D	E	F	G	H	I
1	LoE	5		LoE	Cost	3	4	5	10
2	Rows	5		1	12	12	12	12	12
3	Result	12		2	22	14	16	18	20
4				3	77	16	20	24	28
5				4	88	18	24	30	36
6				5	99	20	28	12	44
7				6	111	22	32	42	52
8				7	222	24	36	48	60
9				8	305	26	40	54	68
10				9	388	28	44	60	76
11				10	471	30	48	66	84
12				11	554	32	52	72	92
13				12	637	34	56	78	100
Sheet1 (2)

Cell Formulas
Range	Formula
B3	B3	=INDEX(Table1[[3]:[10]],MATCH($B$1,Table1[LoE],0),MATCH($B$2&"",Table1[[#Headers],[3]:[10]],0))

 

[jasonb75]

b1 needs to match data in column D

Column D contains a list of consecutive numbers starting at 1 so you don't need to match, you just use the number in B1 to identify the row position.

I forgot that table headers are always text format, so the column match needs a slight edit.

=INDEX(Table1[[3]:[10]],B1,MATCH(B2&"",Table1[[#Headers],[3]:[10]],0))

Looking at the image in post 1, although not definite, it appears that everything in the table could be in text format.

 

[Mike2502]

Thanks guys, really appreciate your help. This has worked

My fault, I should have noted you're using a Table. To search the Header I'll also need to change the B2 5 to text, hence the &""

Mike2502-Q2.xlsx
	A	B	C	D	E	F	G	H	I
1	LoE	5		LoE	Cost	3	4	5	10
2	Rows	5		1	12	12	12	12	12
3	Result	12		2	22	14	16	18	20
4				3	77	16	20	24	28
5				4	88	18	24	30	36
6				5	99	20	28	12	44
7				6	111	22	32	42	52
8				7	222	24	36	48	60
9				8	305	26	40	54	68
10				9	388	28	44	60	76
11				10	471	30	48	66	84
12				11	554	32	52	72	92
13				12	637	34	56	78	100
Sheet1 (2)

Cell Formulas
Range	Formula
B3	B3	=INDEX(Table1[[3]:[10]],MATCH($B$1,Table1[LoE],0),MATCH($B$2&"",Table1[[#Headers],[3]:[10]],0))

Column D contains a list of consecutive numbers starting at 1 so you don't need to match, you just use the number in B1 to identify the row position.

I forgot that table headers are always text format, so the column match needs a slight edit.

=INDEX(Table1[[3]:[10]],B1,MATCH(B2&"",Table1[[#Headers],[3]:[10]],0))

Looking at the image in post 1, although not definite, it appears that everything in the table could be in text format.

 

[Toadstool]

You're welcome!