VLOOKUP deciding to give up the ghost...

[rlmays]

I am using VLOOKUP in a large data set, but in a simple VLOOKUP formula.

=vlookup(L:L,U:V,2,false)

L is a column of concatenated part numbers and units of measure: 100009EA, 100009BX, 100009CA for example
U is the same as L and V is a number

My results are like this:

100009EA 1
100009CA #N/A
100009EA 1
100009EA 1

And yes, I have manually checked to make sure the numbers exist for the 100009CA version. It happening to various numbers in various areas on the spreadsheet? Any ideas about what my problem might be?

Thanks, rlmays

 

[sheetspread]

Your first term should be a single cell or value, not a range.

 

[Joe4]

Assuming what sheetspread said is true, and maybe you just mistyped your equation in your question (and you do actually have one cell in the first argument), the issue could be the following.

Quite often, there might be an extra space or some special character at the end of one of your values that is messing things up.

An easy way to tell. If you see one that isn't working, but you think should be. Let's say that you think L2 should matching up to U100. Try this equation:
=L2=U100
If that returns FALSE, then regardless of what you may think, those values are, in fact, NOT equal.

So then you can proceed to check the length of each one:
=LEN(L2)
=LEN(U100)
If those do not return the same number, than one of them has an extra character in there.