Getting #n/a in a cell doing index and match

[cubs]

I'm not sure why this started happening. It seemed to be working fine and now for some reason I'm getting a #n/a error. My index and match formula look correct. I've deleted and retyped them more times then I care to admit. Not sure why I keep getting #n/a. Also looked at my formula with alt+tuf i see the line for #n/a but not sure how to correct it. I'm going crazy with this one.

=INDEX(MIXEDCHARTS!A1:BD3005,MATCH(MANUALUPDATE!E12,MIXEDCHARTS!A5:A3005),MATCH(F17,MIXEDCHARTS!B1:BD1,0))

I've tried going back in and adding single quotes around my worksheet names too. Still can't get this to work for me.

Help...Please

 

[Peter_SSs]

It is a bit hard without your data and knowing what you named ranges are.

1. Is this red 5 supposed to be a 5 or a 1?
=INDEX(MIXEDCHARTS!A1:BD3005,MATCH(MANUALUPDATE!E12,MIXEDCHARTS!A5:A3005),MATCH(F17,MIXEDCHARTS!B1:BD1,0))


2. In vacant cells, put these formulas and tell us what they return.

=MATCH(MANUALUPDATE!E12,MIXEDCHARTS!A5:A3005)
=MATCH(F17,MIXEDCHARTS!B1:BD1,0)

3. Is column A of MIXEDCHARTS in ascending order?

 

[snoopyhr]

<>

 

[cubs]

Thank you for the quick replies. I tried breaking up my formula into two parts, good idea by the way.

The first one returned the correct number; =MATCH(MANUALUPDATE!E12,MIXEDCHARTS!A5:A3005) The second one however(=MATCH(Sheet2!F17,MIXEDCHARTS!A1:BD1,0) gave me the dreaded #n/a error. Oddly enough when I remove the value out of f17 the number 2 appears in the cell that had the #n/a?? I just wish 2 was the number I was looking for. Any thoughts?

 

[cubs]

I just realized that I could quick reply directly to you. Thank you for your help. I tried breaking up my formula into two parts, good idea by the way.

The first one returned the correct number; =MATCH(MANUALUPDATE!E12,MIXEDCHARTS!A5:A3005) The second one however(=MATCH(Sheet2!F17,MIXEDCHARTS!A1:BD1,0) gave me the dreaded #n/a error. Oddly enough when I remove the value out of f17 the number 2 appears in the cell that had the #n/a?? I just wish 2 was the number I was looking for. Any thoughts?

 

[Peter_SSs]

The second one however(=MATCH(Sheet2!F17,MIXEDCHARTS!A1:BD1,0) gave me the dreaded #n/a error. Any thoughts?

Yes, the value that was in Sheet2!F17 does not occur in A1:BD1 of the sheet MIXEDCHARTS.

Oddly enough when I remove the value out of f17 the number 2 appears in the cell that had the #n/a??

Is there a 0 in B1 of MIXEDCHARTS?

 

[cubs]

The value is in there, it's a percentage match on both sides 20.0% There was a 0.0% in my chart in position a1 but i changed it to an unused % now if I reference an empty cell in my non working formula it no longer displays a 2 it just says #n/a I'm completely stumped by this one.

 

[Peter_SSs]

The value is in there, it's a percentage match on both sides 20.0% There was a 0.0% in my chart in position a1 but i changed it to an unused % now if I reference an empty cell in my non working formula it no longer displays a 2 it just says #n/a I'm completely stumped by this one.

Your formula is looking for an exact match. If any of the values are calculated, it is likely that the underlying number is not exactly what is appearing in the cell. For example, a cell may be showing 20.0% (=0.20) but the underlying number in the cell may be something like 0.2000001

Are the values in row 1 of MIXEDCHARTS the result of formulas or manually typed in?

Your formula in F17 (or wherever) may need changing to something like
=ROUND(existing_formula,3)
I'm not certain about the number of decimal places but you can investigate that.

 

[cubs]

Finally figured it out; FORMATTING, FORMATTING, FORMATTING! Going between several different sheets can be dangerous especially when each cell being referenced has a different format; percentage, text, general I think I had all three. I finally retyped all my headings using the text format. It seemed to do the trick. Thanks to all those who helped me out and contribute to this site. I love this site.

 

[Peter_SSs]

Finally figured it out; FORMATTING, FORMATTING, FORMATTING! ... Thanks to all those who helped me out and contribute to this site. I love this site.

Glad you got there in the end. Thanks for the feedback.