VLOOKUP Keeps Returning #N/A

[Ric_M]

I have 2 columns of account numbers and all I'm trying to do is identify
which accounts in Column A are also in Column B.

The formula I'm using is vlookup(A2,B:B,1,False) and I keep receiving the
#N/A stating "A value is not available to the formula or function."

Column A has the ' at the beginning of the account numbers, I believe in
order to prevent the 17 digit number from being concatenated. There are
approx. 42k numbers/cells.
Column B has the 17 digit numbers as well but no '. The numbers do appear to
have some extra spaces at the end of the cell for some reason. There are
approx. 5k numbers/cells

I did sort Column B (the table array) in ascending order.

I don't know if the formatting, ', or spaces is affecting the formula. When
I use True instead of False, I do get a result but its wrong.

 

[Niek Otten]

Use

vlookup(TRIM(A2),B:B,1,False)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I have 2 columns of account numbers and all I'm trying to do is identify
| which accounts in Column A are also in Column B.
|
| The formula I'm using is vlookup(A2,B:B,1,False) and I keep receiving the
| #N/A stating "A value is not available to the formula or function."
|
| Column A has the ' at the beginning of the account numbers, I believe in
| order to prevent the 17 digit number from being concatenated. There are
| approx. 42k numbers/cells.
| Column B has the 17 digit numbers as well but no '. The numbers do appear to
| have some extra spaces at the end of the cell for some reason. There are
| approx. 5k numbers/cells
|
| I did sort Column B (the table array) in ascending order.
|
| I don't know if the formatting, ', or spaces is affecting the formula. When
| I use True instead of False, I do get a result but its wrong.

 

[M Kan]

Both the spaces and the ' will affect the VLOOKUP. VLOOKUP is literally
looking for exact matches. You can try adding a column and using the VALUE
function to convert the cells with the ' and spaces to regular numbers.

 

[Ric_M]

thank you for your quick reply. Your suggestion didn't work. Still getting
the same error. Is the TRIM command to ignore the ' ? Do I need to be
concerned about the spaces following all the numbers in my second column?

 

[Ric_M]

Thansk for the reply. When I use VALUE, the numbers are concatenated and I
don;t want that.

 

[Niek Otten]

Did you get your data from the web? Then there may be non-breaking spaces in them, which are not removed by the TRIM() function.
In that case, have a look here:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| thank you for your quick reply. Your suggestion didn't work. Still getting
| the same error. Is the TRIM command to ignore the ' ? Do I need to be
| concerned about the spaces following all the numbers in my second column?
|
| "Niek Otten" wrote:
|
| > Use
| >
| > vlookup(TRIM(A2),B:B,1,False)
| >
| > --
| > Kind regards,
| >
| > Niek Otten
| > Microsoft MVP - Excel
| >
| > |I have 2 columns of account numbers and all I'm trying to do is identify
| > | which accounts in Column A are also in Column B.
| > |
| > | The formula I'm using is vlookup(A2,B:B,1,False) and I keep receiving the
| > | #N/A stating "A value is not available to the formula or function."
| > |
| > | Column A has the ' at the beginning of the account numbers, I believe in
| > | order to prevent the 17 digit number from being concatenated. There are
| > | approx. 42k numbers/cells.
| > | Column B has the 17 digit numbers as well but no '. The numbers do appear to
| > | have some extra spaces at the end of the cell for some reason. There are
| > | approx. 5k numbers/cells
| > |
| > | I did sort Column B (the table array) in ascending order.
| > |
| > | I don't know if the formatting, ', or spaces is affecting the formula. When
| > | I use True instead of False, I do get a result but its wrong.
| >
| >
| >

 

[Ric_M]

Great news! I used TRIM for both columns to get rid of the spaces and ' and
the vlookup worked. Thansk so much.