Match returning N/a-

[JohnH58]

I have used the match() function many times. I am using a simple match function but why it is returning an error has me puzzled. I have added an image of the spreadsheet here.
1. Column L is formatted as a number with 2 decimals.
2. My table array is in Column AI Rows 1352 - 1366. This is also formatted as a number with 2 decimal places.
3. Above my table array in rows 1341-1346 is the formula returning the N/a in all but 1 cell.
4. The formula I am using is =MATCH(L1346,$AI$1352:$AI$1366,0). For clarity L1346 is moving with each cell I copy.
5. Each of the values I am looking for exist so I am using the (0-Exact Match) option in the match.
6. I have tested each of the cells for blanks etc using the Len() function and all appears fine.

This should be a relatively simple formula, I am using it in a more complex Index,Match function but have only shown the match problem here as that is the element of the formula that is causing problems.
I would appreacte any steer I can get on this one, it has me stumped!

JohnH

 

[hernantorres23]

Yes it is. However, what I don't understand is the formula is no different to mine that is returning the error result. See screenshot attached displaying both formulas. I am still stumped?

Exactly I didn't any change anything, and your formulas look good. Both Fluff tests suggested in my case result in zero and TRUE, respectively.

 

[JohnH58]

=L1344=AI1361

It returns True.

 

[JohnH58]

Ok, what if you use

=L1344=AI1361

It return TRUE

 

[Fluff]

In that case I'm not sure what is happening.
In your actual workbook are col L & AI1352:AI1366 formula or hard numbers?

 

[JohnH58]

In that case I'm not sure what is happening.
In your actual workbook are col L & AI1352:AI1366 formula or hard numbers?

They are both hard numbers. Column L was originally populated by a VBA form I have. However, I re-typed them in to make sure that the population method wasn't the cause.

 

[Fluff]

I re-typed them in to make sure that the population method wasn't the cause.

Did that help?

 

[JohnH58]

Did that help?

No, the error is still there. You can see the actual formulas, etc in the XL2BB that I loaded up.

 

[Fluff]

In that case I'm afraid I can't help any further.
There is nothing wrong with the formula & if I paste that into Excel, the formula works.
If those cells had formulae then it could have been rounding errors, but as they are hard values that shouldn't be a problem.

 

[JohnH58]

In that case I'm afraid I can't help any further.
There is nothing wrong with the formula & if I paste that into Excel, the formula works.
If those cells had formulae then it could have been rounding errors, but as they are hard values that shouldn't be a problem.

Thanks anyway. I have used this formula many times also and it doesnt make any sense at all what it is doing right now.