Matrix Table Lookups?

[lukman4068]

Hi guys,

I've been stumped on how to do this!

I need excel to be able to populate that Points field automatically depending on what the start and end cycles are.

Is there some sort of VLookup formula that you could use? Any help would be much appreciated!

Thanks!

 

[Norie]

There's probably a better formula but try this in D10 and copy down.

=INDEX($C$3:$F$6,C10+1,B10+1)

 

[jcaptchaos2]

Excel Workbook
	C	D	E	F	G	H	I	J	K	L
2							Start Cycle
3							0	1	2	3
4	Start Cycle	End Cycle	Points			0	0.01	0.025	0.029	0.031
5	3	2	0.065			1		0.041	0.049	0.056
6	2	1	0.041			2			0.065	0.027
7	0	0	0			3				0.086
8	3	0	0.029
Sheet1

 

[lukman4068]

that actually works! Sorry if it sounds silly, but i'm pretty new to really getting into using excel - but how does that actual formula work and how do you build it up to do what you've got it doing?

Thanks! :D

 

[jcaptchaos2]

Excel Workbook
	C	D	E	F	G	H	I	J	K	L	M
2							Start Cycle
3							0	1	2	3
4	Start Cycle	End Cycle	Points			0	0.01	0.025	0.029	0.031
5	3	2	0.027			1		0.041	0.049	0.056
6	2	1	0.049			2			0.065	0.027
7	0	0	0.01			3				0.086
8	3	0	0.031
9
Sheet1

 

[sanrv1f]

may be this

assuming data is in C1:G6 and result table is in C14:E18

E15

=INDEX($C$2:$G$6,MATCH(D15,$C$2:$C$6,0),MATCH(C15,$C$2:$G$2,0))

and copied down

 

[lukman4068]

thanks for the help! it works fantastically! :D