Formula N/A Error

[Glasgowsmile]

Good Afternoon,

I'm getting an error with my formula... but only sometimes not every time.

=INDEX(DlyRateCodes1!$D$27:$K$455,MATCH($CD11,DlyRateCodes1!$B$27:$B$455,0),MATCH(H$1000,DlyRateCodes1!$D$26:$Z$26,0))

If you could give me an idea of what's wrong, that would be great. I know this is vague but I can't exactly share the spreadsheet.

I tried to evaluate the process and see if I could pin-point the issue. After the first step it throws a #N/A into the formula

Before starting Evaluation:
https://imgur.com/SchoZoZ

You'll see the #N/A error in the formula now:
https://imgur.com/8NDX7QH

After change the exact match to less or greater than, it at least doesn't give me an error message but exact is what I need and what it's trying to match does exist within that array so I don't understand why it's missing it

 

[JoeMo]

Good Afternoon,

I'm getting an error with my formula... but only sometimes not every time.

=INDEX(DlyRateCodes1!$D$27:$[COLOR=#ff0000][B]K[/B][/COLOR]$455,MATCH($CD11,DlyRateCodes1!$B$27:$B$455,0),MATCH(H$1000,DlyRateCodes1!$D$26:$[B][COLOR=#ff0000]Z[/COLOR][/B]$26,0))

If you could give me an idea of what's wrong, that would be great. I know this is vague but I can't exactly share the spreadsheet.

I tried to evaluate the process and see if I could pin-point the issue. After the first step it throws a #N/A into the formula

Before starting Evaluation:
https://imgur.com/SchoZoZ

You'll see the #N/A error in the formula now:
https://imgur.com/8NDX7QH

Have you tried making the column letter Z (in red) the same as the last column (K) in the array? The formula can't find something that's beyond the last column of the array.

 

[Glasgowsmile]

Have you tried making the column letter Z (in red) the same as the last column (K) in the array? The formula can't find something that's beyond the column of the array.

Good Point, I corrected that but weirdly that wasn't the issue.

I found the problem to be $CD11 -- that cell was using =Trim($A2), because I couldn't get it to work referencing A2 directly due to the formula it was using. (posted below)

='ActvRateCat SumRpt'!F18

Trim worked no problem, unless A2 was a number.

So if A2 said VA -- No problem.
If A2 said 23 then it would error out, though I don't understand why.

I changed it to =A2 instead and that works fine.