VLookup error #N/A

Robert Edens · Mar 11, 2011

VLookup formula Error “#N/A”
Operating System: MS XPPro
Excel Version: 2007
I am addressing only two of the worksheets in my workbook, “Tool” and “VLU”.
The “Tool” worksheet contains manufacturer part numbers in Column B – nearly 50,000 rows. It is the main worksheet in my workbook. It is used to provide quotes to my customers and has many formulas within.
The “VLU” worksheet contains a database provided by my supplier with nearly 50,000 rows containing unique part numbers, descriptions, list price, costs and manufacturer name.
I need to update my pricing information weekly and receive an excel file from my supplier weekly. I then copy and paste the updated information in place of the existing data in the “VLU” worksheet. When I receive the update from my supplier the part number is left justified in the cell and my understanding is that this indicates “text format.” I have attempted changing the format to number, text, general in both worksheets, but it still produces the “#N/A” error in my “Tool” worksheet within the cell with the VLookup formula.
I have the B Column “Part Numbers” in my “Tool” worksheet set as general format.
I spent some time searching the internet and found a solution, but it is not practical. The solution is to select the “VLU” worksheet and select the part number in row 1 and then press F2 and then enter. At the bottom of the worksheet I observe the computer is using the processor to update a calculation. The processing last from 3 to 5 seconds per each Part Number cell that execute this command. When I select the “Tool” worksheet, each of the part numbers that I have executed that command too have resolved and now illustrate that correct information in the VLookup Formula Cell. This method is not practical due to the nearly 50,000 cells that I have to execute this against.
I am exploring writing a Macro that performs this command on a range basis, but even if I am successful, the Macro when executed will take many hours to process before it is complete.
Is there a way to import this data weekly and insure that my existing workbook will accept the new pricing information if I prepare it properly and make the necessary changes to the worksheet before I cut and paste it in?

Gerald Higgins · Mar 11, 2011

Hi, welcome to the board.

First of all, left justification does not itself indicate a text string. Numbers can be left justified (or aligned) as well.
But your data may well be a text string.

There are ways to force Excel to compare a text string with a number, and vice versa.

Let's say your lookup value is in cell A1.
If it's a number, and the values in your lookup table are all text, you can use this

Code:

=vlookup(a1&"",[insert rest of vlookup formula here])

If it's the other way round, and a1 is a text string but your lookup table is all numbers, you can use this

Code:

=vlookup(a1+0,[insert rest of vlookup formula])

baitmaster · Mar 11, 2011

left justification is not necessarily text. If you are getting N/A, there is a difference between the two values - possibly a hanging space?

Check the formats are the same, then re-enter the values to ensure they abide by the new format (thats what F2 > Enter is doing). If this corrects the error, one is text and the other is numeric

I think the code you want might be as follows (select range in worksheet then run macro):

Rich (BB code):

dim cl as range
 
application.Calculation = xlCalculationManual ' this will speed up Excel by preventing the recalculations you saw
 
for each cl in selection
    cl.value = cl.value ' if this doesn't work, try .text
next cl
 
application.Calculation = xlCalculationAutomatic

WARNING: if there are any formulas, you will lose them. You will also lose the UNDO facility, so your work will be permanently ruined! Back up your work first!

Robert Edens · Mar 11, 2011

Thank you Gerald Higgins. I tested this against several rows and it appears to resolved. I will test it later today against the whole worksheet.

VLookup error #N/A

Robert Edens

New Member

Excel Facts

Gerald Higgins

Well-known Member

baitmaster

Well-known Member

Robert Edens

New Member

Similar threads

Forum statistics

Share this page

VLookup error #N/A

Robert Edens

New Member

Excel Facts

Gerald Higgins

Well-known Member

baitmaster

Well-known Member

Robert Edens

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock