Vlookup

[jrpeel22]

I am trying to perform a vlookup and I am getting the #N/A error.

I am trying to compare 2 spreadsheets that have identical account numbers on them with different corresponding information.

The accounts numbers begin with an apostrophe before displaying the account number (i.e. '12345678).

I found the following excerpt on the Excel website:

If lookup_value is smaller than the smallest value in the first column of table_array, VLOOKUP returns the #N/A error value.

Here is my formula:

=VLOOKUP(A2,L2:BY12542,2,FALSE)

I would greatly appreciate any help you may provide. Thanks!

 

[Jonmo1]

Welcome to the board...

The part you bolded only applies when you use TRUE as teh 4th argument in the vlookup.
And you really only use that when you want to do a "Closest match" lookup.
In this case, you likely want an exact match...

Do the account #s begin with ' on BOTH sheets?

What does this return

=ISNUMBER(A1)

Where A1 is one of your account numbers.
Repeat this on BOTH sheets.

 

[jrpeel22]

Yes, the aposotrophe is on both account numbers.

I put in the ISNUMBER function and it returned FALSE for both account numbers.

 

[Matty]

If you do a manual find, can you locate the value in A2 in the table_array L2:BY12542?

Matty

 

[Aladin Akyurek]

Do you expect to return a number or some text? What is your version of Excel?

 

[jrpeel22]

Matty,

When I got "Find and Select" it is unable to locate the account number that I have in the A2 field. However, when I simply search my table I am able to locate the matching account number.

 

[Matty]

There could be an extraneous blank space (or spaces) in either A2 or where this account number resides in L2:BY12542.

Does this return the expected result?

=VLOOKUP(TRIM(A2),TRIM(L2:BY12542),2,FALSE)

This formula needs committing with CTRL+SHIFT+ENTER.

Matty

 

[jrpeel22]

Matty,

I think I have figured out what is holding me back but now I need to figure out how to systematically fix it.

As I mentioned in an earlier post... each account number has an apostrophe in front of it. I removed the apostrophe from cell A2 and from its corresponding account number in the table array and it displayed my intended result.

Is there a way to quickly remove the apostrophe in front of each individual account number?

 

[jrpeel22]

I have figured out how to alter the account number so I can get rid of the aposotrophe.

I have come up with this equation:

=RIGHT(TRIM(A7767),14)

Thanks for the help!

 

[Matty]

An alternative way:

=SUBSTITUTE(A7767,"'","")

You may still have problems though, as the above will still be considered 'text'. If the account numbers on the other list are considered 'numbers', the lookup will fail. If they are also 'text' though, things will be fine.

Matty