VLOOKUP Keeps Returning #N/A
 

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.

VLOOKUP Keeps Returning #N/A
 

Use

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Ric_M" wrote in message ...
|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.

VLOOKUP Keeps Returning #N/A
 

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.
--
Tips for Excel, Word, PowerPoint and Other Applications
http://www.kan.org/tips


"Ric_M" wrote:

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.

VLOOKUP Keeps Returning #N/A
 

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

"Ric_M" wrote in message ...
|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.

VLOOKUP Keeps Returning #N/A
 

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

"M Kan" wrote:

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.
--
Tips for Excel, Word, PowerPoint and Other Applications
http://www.kan.org/tips


"Ric_M" wrote:

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.

VLOOKUP Keeps Returning #N/A
 

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 he

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Ric_M" wrote in message ...
| 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
|
| "Ric_M" wrote in message ...
| |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.
|
|
|

VLOOKUP Keeps Returning #N/A
 

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

"Niek Otten" wrote:

Use

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Ric_M" wrote in message ...
|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.

Hi

Here is also a good video that covers all common examples when vlookup keeps returning na and at the top there are links to more videos related to vlookup.

Take care
Katie & Emil