using index and match --problem with formula-- sometimes get a match and sometimes when there is data in the table get N/A

[cgat]

					​	​	​
worksheet table​					​	​	​
y x 0.1​ 0 (0.1) (0.2) (2.3)​ (0.2769) (0.1216) (2.4)​ (2.5)​ 0.0167 (0.0624) (2.6)​ (2.7)​ 0.0698 (0.1279) 0.0923 (2.8)​ 0.0692 (0.1111)					​	​	​
​					​	​	​
​	predictions		formula used		​	​	​
​	y x prediction -2.3​ -0.1​ -0.12162​ -2.3​ 0​ -0.2769​ -2.5​ -0.2​ #N/A​ -2.5​ 0​ 0.016675​ -2.7​ -0.2​ #N/A​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​ ​		=INDEX($K$37:$P$43,MATCH(T36,$K$37:$K$43,0),MATCH(U36, $K$37:P$37,0))		​	​	​
I cannot figure out what I am doing wrong. I would greatly appreciate any help.​					​	​	​
					​	​	​

 

[C Moore]

Did you step through with Evaluate Formula to see if you're getting the right number returned for your matches?

 

[cgat]

I apparently am too clueless to use this function. I tried to go to the Evaluate formula tab and I am not picking up on my problem.

 

[AhoyNC]

Check to make sure you have a match where the formula is giving you #N/A.
I'm assuming that -0.2 is in P36 and the match value is in U28 (change the cells reference if I guessed wrong)
Does =P36=U38 equal true or false?
Does =K39=T38 equal true or false (again change cell reference if needed)?
If you get a false check to see if one cell may be numeric and the other text.

 

[cgat]

AhoyNC , You are correct on your assumption on -.2 being P36.
=p36=u38 equals true.
=K39=T38 equals true.

Thank you for your help.
I realize I am doing something really stupid, but I cannot find my mistake.

 

[AhoyNC]

Your formula works for me.

Book1
	K	L	M	N	O	P	Q	R	S	T	U	V	W
34
35										y	x		prediction
36										-2.3	-0.1		-0.1216
37	y	x	0.1	0	-0.1	-0.2				-2.3	0		-0.2769
38	(2.30)			-0.2769	-0.1216					-2.5	-0.2		-0.0624
39	(2.40)									-2.5	0		0.0167
40	(2.50)			0.0167		-0.0624				-2.7	-0.2		0.0923
41	(2.60)
42	(2.70)		0.0698	-0.1279		0.0923
43	(2.80)			0.0692	-0.1111
44
45
Sheet1

Cell Formulas
Range	Formula
W36:W40	W36	=INDEX($K$37:$P$43,MATCH(T36,$K$37:$K$43,0),MATCH(U36, $K$37:P$37,0))

 

[cgat]

AhoyNC,

Thank you for running this and verifying that the formula works.
Should I start from scratch to hopefully catch what problem I have put in the system?
I cannot not seem to be able to figure out where I have instigated the problem.

 

[AhoyNC]

If it's a small data set that would probably be a good option.
What is the actual formula that's in the cell giving you the #N/A?

 

[cgat]

=if(t21=0,"",t21)

 

[AhoyNC]

I thought you said the issue was with the INDEX formula.
Where did the IF function come from?