#N/A Error in Index/Match

[kathryng]

I am consistently receiving an #N/A Error when using an Index formula with Match.

The formula I currently have is this:
=INDEX(N2:N5,MATCH(L2,O2:O5,0))

L	M	N	O
4		A	3
4		V	4
5		M	5
5		D	6

<tbody>
</tbody>

I want the formula to provide me the letter in column N which corresponds to O when matched against L (so, in this example, the results should be V, V, M, M).

My problem is that the formula works if I replace L2 with the actual number in the formula, but not with a reference to a cell. I've used this formula before and I have no idea what I am doing wrong.

Please advise, and if I can provide additional information, I am happy to do so. Thank you!

 

[VBA Geek]

it looks fine, maybe you have numbers not formatted as number but as text hence you get N/A, or some extra spaces you're not aware of

 

[Jonmo1]

Sounds like the numbers in column L are not really numbers, but "numbers stored as text"

Does this work?
=INDEX(N2:N5,MATCH(L2+0,O2:O5,0))

 

[kathryng]

This works, thank you so much! I feel so silly.

 

[Jonmo1]

Glad to help.

But FYI, that was really just an attempt to isolate the problem
If that worked, it means the values in L are not really numbers.

I would recommend converting them to real numbers, instead of manipulating them in the formula

Copy a blank cell
Highlight column L
Right Click - Paste Special - Values - Add - OK

Then your original formula should work.