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

cgat

New Member
Joined
Sep 22, 2022
Messages
7
Office Version
  1. 2019
Platform
  1. Windows
worksheet table​
yx
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)
predictionsformula used



yxprediction
-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.​
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

C Moore

Well-known Member
Joined
Jan 17, 2014
Messages
626
Office Version
  1. 365
Platform
  1. Windows
Did you step through with Evaluate Formula to see if you're getting the right number returned for your matches?
 

cgat

New Member
Joined
Sep 22, 2022
Messages
7
Office Version
  1. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Oct 10, 2011
Messages
4,971
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Sep 22, 2022
Messages
7
Office Version
  1. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Oct 10, 2011
Messages
4,971
Office Version
  1. 365
Platform
  1. Windows
Your formula works for me.

Book1
KLMNOPQRSTUVW
34
35yxprediction
36-2.3-0.1-0.1216
37yx0.10-0.1-0.2-2.30-0.2769
38(2.30)-0.2769-0.1216-2.5-0.2-0.0624
39(2.40)-2.500.0167
40(2.50)0.0167-0.0624-2.7-0.20.0923
41(2.60)
42(2.70)0.0698-0.12790.0923
43(2.80)0.0692-0.1111
44
45
Sheet1
Cell Formulas
RangeFormula
W36:W40W36=INDEX($K$37:$P$43,MATCH(T36,$K$37:$K$43,0),MATCH(U36, $K$37:P$37,0))
 

cgat

New Member
Joined
Sep 22, 2022
Messages
7
Office Version
  1. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Oct 10, 2011
Messages
4,971
Office Version
  1. 365
Platform
  1. Windows
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?
 

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,971
Office Version
  1. 365
Platform
  1. Windows
I thought you said the issue was with the INDEX formula.
Where did the IF function come from?
 

Forum statistics

Threads
1,176,055
Messages
5,901,137
Members
434,875
Latest member
Leopoldo Blancas

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
Top