Vlookup display as n/a

Chris1973

Board Regular
Joined
Apr 17, 2020
Messages
115
Office Version
  1. 2016
Platform
  1. Windows
Hi Guys.

Can you please give me some advise on what may be wrong.

I am using v lookup with no problems.
All of a sudden when i create a new article number in the lookup file, and then enter that number in my quote does it display as N/A
All numbers are displayed correctly, the formula allows to look for the new lines being entered, i made sure enough lines are covered
If i enter the same number as previous numbers in that line does it doe the lookup, just the new numbers that i create that does not work
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
The "N/A" means it is not finding a match for that particular value.

Note that one common issue people have is that VLOOKUP will only match up text-to-text or number-to-number.
It will NOT work if you try to match up a number to a number entered as text.

It is real easy to confirm this with as ISNUMBER function, i.e.
Excel Formula:
=ISNUMBER(A1)

Try that on the two values which you think should match.
If one returns TRUE and one returns FALSE, then you have two different data types that will never work in a VLOOKUP.
They must be the same.
 
Upvote 0
Thanks for the feedback Joe.

I have done this, if i enter a value that must work( worked previously) does it work 100%

I f then change that to the new value, no go just get a n/a response

The format of the cells are the same, they are also the same as the one that is workin. I have tried under general then numbers,then text and then custom no joy
 
Upvote 0
Thanks for the feedback Joe.

I have done this, if i enter a value that must work( worked previously) does it work 100%

I f then change that to the new value, no go just get a n/a response

The format of the cells are the same, they are also the same as the one that is workin. I have tried under general then numbers,then text and then custom no joy
Changing the format only changes the appearance of the cell, it will not change how the data was entered (as "number" or as "text").
And formatting only affects entries entered as "numbers". So, if you try changing the format of a cell, and it does not change its appearance, then you are dealing with a "text" entry.

Please show us an example that is not working for you. Show us both your formula, and the value it is looking up, and the section of your data it is supposed to be matching.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
 
Upvote 0
071304005CARGO TIEDOWNR10,00
08901087BRAKE CLEANERR10,00
W067100002COVER FOR COOLIUS AIRCON 10 SERIESR940,00
9500500455ALL SURFACES PVA WATER BASED PAINT BRUSH 38MMR43,75
this is the main document

This is my invoce document

071304005CARGO TIEDOWN1R 10,000%R10,00
08901087BRAKE CLEANER1R 10,000%R10,00
W067100002COVER FOR COOLIUS AIRCON 10 SERIES1R 940,0015%R799,00
9500500455
#N/A​
1
#N/A​
#N/A#N/A

=VLOOKUP(B12;'[MASTER LIST Rep and customer list July 2021.xlsx]Pricelist'!$A$2:$B$99999;2;FALSE)
 
Upvote 0
Does row "9500500455" on your Main document already exists when you first open that file, or is it being added after the file is opened?
If it is being added after the file is open, try saving that file, and then see if that formula in your other workbook works.

What cell does "9500500455" appear in on your Main document?
Let's say it is cell A5.
Then in any blank cells in your Main document, enter these two formulas and tell me what they return:
=ISNUMBER(A5)
=LEN(A5)


I am assuming that "9500500455" appears in cell B12 on your Invoice document.
So, enter these two formula in any blank cells on your Invoice document and tell me what they return:
=ISNUMBER(B12)
=LEN(B12)
 
Upvote 0
Hi Joe

On the first "command"
FALSE
12

On the second command
True
10
 
Upvote 0
On the first "command"
FALSE
12
That means this first one is NOT a numeric entry, but rather a text one, and has a length of 12.

On the second command
True
10
That means the second one is a numeric, and has a length of 10.

As I said earlier:
Note that one common issue people have is that VLOOKUP will only match up text-to-text or number-to-number.
It will NOT work if you try to match up a number to a number entered as text.

In order for VLOOKUP (or INDEX/MATCH or any other LOOKUP function) to work, two things MUST be true:
1. The values you are trying to match on MUST be the same data-type (BOTH must be numeric, or BOTH must be text)
2. In order to find a match using these functions, they must match EXACTLY (including all matches MUST be the same length)

Based on the results of your last post, your data meets neither of these two requirements, so they are not a match.

So it looks like you have a little data clean-up to do to get the two data lists using the same data type (make them ALL numbers or make them ALL text).
As I mentioned before:
Changing the format only changes the appearance of the cell, it will not change how the data was entered (as "number" or as "text").
And formatting only affects entries entered as "numbers". So, if you try changing the format of a cell, and it does not change its appearance, then you are dealing with a "text" entry.
If you want to change the data types on data that is already entered, you can use "Text to Columns" to re-enter the whole column of data again at once.
 
Upvote 0
Ok thanks for this, with my limited knowledge does this make sense.

Last question then, as i am not sure what you mean by

"If you want to change the data types on data that is already entered, you can use "Text to Columns" to re-enter the whole column of data again at once."

Would you mind explaining what i need to do. I honestly have no idea what you mean by this
 
Upvote 0
Ok thanks for this, with my limited knowledge does this make sense.

Last question then, as i am not sure what you mean by

"If you want to change the data types on data that is already entered, you can use "Text to Columns" to re-enter the whole column of data again at once."

Would you mind explaining what i need to do. I honestly have no idea what you mean by this
OK, you need to do this on the two columns that contain the IDs you are matching on:

First list:
1. Select the column that contains the ID
2. From the "Data" menu, select "Text To Columns"
3. Click the "Next" button twice until you get to Step 3
4. Make sure it is set to "General"
5. Click "Finish"

Then go to your second list, and perform the exact same steps on that list.

This should convert the two ID lists you are comparing to all numbers, and then your VLOOKUP should work.

Note that in Step 4, you could choose "Text" if you want those IDs to be Text and not Numeric. But then just note if extra spaces are found in either list, they could still cause problems.
 
Upvote 0
Solution

Forum statistics

Threads
1,213,484
Messages
6,113,923
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top