VLookup formula and #N/A

M

mad3

Board Regular
Joined
Sep 15, 2009
Messages
128
Office Version
  1. 365
Platform
  1. Windows
I have a VLOOKUP formula that works on the first cell it is used. However, it fails on all subsequent rows.

This is my formula...in cell V2 where it works. Then when copied to V3 it fails.
=VLOOKUP(W2,'Engineering'!$C$2:$J$375,8,FALSE)

This is the formula I have in V3...
=VLOOKUP(W3,'OSP Engineering'!$C$2:$J$375,8,FALSE)

It returns this for a result on this row and all subsequent rows.
#N/A



This is where VLOOKUP search data is coming from...thanks for the help!
[TABLE="width: 134"]
<tbody>[TR]
[TD="class: xl67, width: 115, bgcolor: transparent"]Mgr
[/TD]
[TD="class: xl69, width: 64, bgcolor: transparent"]Exchange
[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]Steve Mendoza
[/TD]
[TD="class: xl68, bgcolor: transparent"]20374
[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"]#N/A
[/TD]
[TD="class: xl70, bgcolor: transparent"]20372
[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"]20684
[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"]20684
[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"]20682
[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"]20682
[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"]20684
[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"]20686
[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"]50157
[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"]20684
[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"]20686
[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"]20374
[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"]70172
[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl70, bgcolor: transparent"]20374
[/TD]
[/TR]
</tbody>[/TABLE]





[TABLE="width: 526"]
<tbody>[TR]
[TD="class: xl67, width: 64, bgcolor: transparent"]Exchange
[/TD]
[TD="class: xl68, width: 113, bgcolor: transparent"]Office Name
[/TD]
[TD="class: xl68, width: 84, bgcolor: transparent"]Order code
[/TD]
[TD="class: xl68, width: 172, bgcolor: transparent"]Division
[/TD]
[TD="class: xl69, width: 87, bgcolor: #BDD7EE"]Engineering
[/TD]
[TD="class: xl69, width: 85, bgcolor: #BDD7EE"]Phone Number
[/TD]
[TD="class: xl70, width: 96, bgcolor: #BDD7EE"]OSP Engineering Manager
[/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: #DBDBDB"]20372
[/TD]
[TD="class: xl72, width: 113, bgcolor: #DBDBDB"]Adelanto
[/TD]
[TD="class: xl73, bgcolor: #DBDBDB"]ADLNCAXF
[/TD]
[TD="class: xl73, bgcolor: #DBDBDB"]South
[/TD]
[TD="class: xl73, bgcolor: #DBDBDB"]Oscar martinez
[/TD]
[TD="class: xl73, bgcolor: #DBDBDB"]123-713-0123
[/TD]
[TD="class: xl73, bgcolor: #DBDBDB"]Todd werth
[/TD]
[/TR]
[TR]
[TD="class: xl74, bgcolor: #F8CBAD"]20374
[/TD]
[TD="class: xl75, bgcolor: #F8CBAD"]ADIN
[/TD]
[TD="class: xl76, bgcolor: #F8CBAD"][/TD]
[TD="class: xl77, bgcolor: #F8CBAD"]North
[/TD]
[TD="class: xl77, bgcolor: #F8CBAD"]Albert rose
[/TD]
[TD="class: xl77, bgcolor: #F8CBAD"]713-123-1234
[/TD]
[TD="class: xl77, bgcolor: #F8CBAD"]Steve Mendoza
[/TD]
[/TR]
[TR]
[TD="class: xl79, bgcolor: #FFFFCC"]20684
[/TD]
[TD="class: xl80, width: 113, bgcolor: #FFFFCC"]Alamitos
[/TD]
[TD="class: xl81, bgcolor: #FFFFCC"]SLBHCAXF
[/TD]
[TD="class: xl81, bgcolor: #FFFFCC"]West
[/TD]
[TD="class: xl81, bgcolor: #FFFFCC"]Oscar martinez
[/TD]
[TD="class: xl81, bgcolor: #FFFFCC"]614-213-1234
[/TD]
[TD="class: xl81, bgcolor: #FFFFCC"]Ray tabler
[/TD]
[/TR]
[TR]
[TD="class: xl82, bgcolor: #F8CBAD"]20682
[/TD]
[TD="class: xl83, width: 113, bgcolor: #F8CBAD"]Alderpoint
[/TD]
[TD="class: xl77, bgcolor: #F8CBAD"]ALPNCAXF
[/TD]
[TD="class: xl77, bgcolor: #F8CBAD"]North
[/TD]
[TD="class: xl77, bgcolor: #F8CBAD"]Albert rose
[/TD]
[TD="class: xl77, bgcolor: #F8CBAD"]615-345-0987
[/TD]
[TD="class: xl77, bgcolor: #F8CBAD"]Steve Mendoza
[/TD]
[/TR]
[TR]
[TD="class: xl79, bgcolor: #FFFFCC"]20684
[/TD]
[TD="class: xl80, width: 113, bgcolor: #FFFFCC"]Aliso (RSU)
[/TD]
[TD="class: xl81, bgcolor: #FFFFCC"]SLGBCAXF
[/TD]
[TD="class: xl81, bgcolor: #FFFFCC"]West
[/TD]
[TD="class: xl81, bgcolor: #FFFFCC"]Oscar martinez
[/TD]
[TD="class: xl81, bgcolor: #FFFFCC"]614-234-0123
[/TD]
[TD="class: xl81, bgcolor: #FFFFCC"]Ray tabler
[/TD]
[/TR]
[TR]
[TD="class: xl79, bgcolor: #FFFFCC"]20686
[/TD]
[TD="class: xl80, width: 113, bgcolor: #FFFFCC"]Alondra
[/TD]
[TD="class: xl81, bgcolor: #FFFFCC"]NRWLCAXG
[/TD]
[TD="class: xl81, bgcolor: #FFFFCC"]West
[/TD]
[TD="class: xl81, bgcolor: #FFFFCC"]Oscar martinez
[/TD]
[TD="class: xl81, bgcolor: #FFFFCC"]657-000-1111
[/TD]
[TD="class: xl81, bgcolor: #FFFFCC"]Ray tabler
[/TD]
[/TR]
[TR]
[TD="class: xl82, bgcolor: #F8CBAD"]70172
[/TD]
[TD="class: xl83, width: 113, bgcolor: #F8CBAD"]Alpaugh
[/TD]
[TD="class: xl77, bgcolor: #F8CBAD"]ALPGCAXF
[/TD]
[TD="class: xl77, bgcolor: #F8CBAD"]North
[/TD]
[TD="class: xl77, bgcolor: #F8CBAD"]Albert rose
[/TD]
[TD="class: xl77, bgcolor: #F8CBAD"]666-555-1111
[/TD]
[TD="class: xl77, bgcolor: #F8CBAD"]Jon Welton
[/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: #DBDBDB"]20374
[/TD]
[TD="class: xl72, width: 113, bgcolor: #DBDBDB"]Antelope
[/TD]
[TD="class: xl73, bgcolor: #DBDBDB"]LNCSCAXF
[/TD]
[TD="class: xl73, bgcolor: #DBDBDB"]South
[/TD]
[TD="class: xl73, bgcolor: #DBDBDB"]Oscar martinez
[/TD]
[TD="class: xl73, bgcolor: #DBDBDB"]111-222-3333
[/TD]
[TD="class: xl73, bgcolor: #DBDBDB"]Todd werth
[/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: #DBDBDB"]20374
[/TD]
[TD="class: xl72, width: 113, bgcolor: #DBDBDB"]Antelope Acres
[/TD]
[TD="class: xl73, bgcolor: #DBDBDB"]LNCSCAXE
[/TD]
[TD="class: xl73, bgcolor: #DBDBDB"]South
[/TD]
[TD="class: xl73, bgcolor: #DBDBDB"]Oscar martinez
[/TD]
[TD="class: xl73, bgcolor: #DBDBDB"]222-111-3131
[/TD]
[TD="class: xl73, bgcolor: #DBDBDB"]Todd werth
[/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: #DBDBDB"]20299
[/TD]
[TD="class: xl72, width: 113, bgcolor: #DBDBDB"]Anza
[/TD]
[TD="class: xl73, bgcolor: #DBDBDB"]ANZACAXF
[/TD]
[TD="class: xl73, bgcolor: #DBDBDB"]South
[/TD]
[TD="class: xl73, bgcolor: #DBDBDB"]Oscar martinez
[/TD]
[TD="class: xl73, bgcolor: #DBDBDB"]222-111-2211
[/TD]
[TD="class: xl73, bgcolor: #DBDBDB"]Bill Karns
[/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: #DBDBDB"]70173
[/TD]
[TD="class: xl72, width: 113, bgcolor: #DBDBDB"]Apple Valley
[/TD]
[TD="class: xl73, bgcolor: #DBDBDB"]APVYCAXF
[/TD]
[TD="class: xl73, bgcolor: #DBDBDB"]South
[/TD]
[TD="class: xl73, bgcolor: #DBDBDB"]Oscar martinez
[/TD]
[TD="class: xl73, bgcolor: #DBDBDB"]111-222-1122
[/TD]
[TD="class: xl73, bgcolor: #DBDBDB"]Todd werth
[/TD]
[/TR]
[TR]
[TD="class: xl82, bgcolor: #F8CBAD"]20476
[/TD]
[TD="class: xl83, width: 113, bgcolor: #F8CBAD"]ARBUCKLE
[/TD]
[TD="class: xl76, bgcolor: #F8CBAD"][/TD]
[TD="class: xl77, bgcolor: #F8CBAD"]North
[/TD]
[TD="class: xl77, bgcolor: #F8CBAD"]Albert rose
[/TD]
[TD="class: xl77, bgcolor: #F8CBAD"]111-333-1111
[/TD]
[TD="class: xl77, bgcolor: #F8CBAD"]Steve Mendoza
[/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: #DBDBDB"]20299
[/TD]
[TD="class: xl72, width: 113, bgcolor: #DBDBDB"]Arrowhead
[/TD]
[TD="class: xl73, bgcolor: #DBDBDB"]ARHDCAXF
[/TD]
[TD="class: xl73, bgcolor: #DBDBDB"]South
[/TD]
[TD="class: xl73, bgcolor: #DBDBDB"]Oscar martinez
[/TD]
[TD="class: xl73, bgcolor: #DBDBDB"]333-111-2222
[/TD]
[TD="class: xl73, bgcolor: #DBDBDB"]Bill Karns
[/TD]
[/TR]
[TR]
[TD="class: xl79, bgcolor: #FFFFCC"]70001
[/TD]
[TD="class: xl80, width: 113, bgcolor: #FFFFCC"]Artesia
[/TD]
[TD="class: xl81, bgcolor: #FFFFCC"]ARTSCAXF
[/TD]
[TD="class: xl81, bgcolor: #FFFFCC"]West
[/TD]
[TD="class: xl81, bgcolor: #FFFFCC"]Oscar martinez
[/TD]
[TD="class: xl81, bgcolor: #FFFFCC"]444-111-2222
[/TD]
[TD="class: xl81, bgcolor: #FFFFCC"]Ray tabler
[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Sort by date Sort by votes
you have changed the sheet the data is coming from, which is probably why is is failing
try changing :
=VLOOKUP(W3,'OSP Engineering'!$C$2:$J$375,8,FALSE)
to
=VLOOKUP(W3,'Engineering'!$C$2:$J$375,8,FALSE)

then it will be the same as you have in cell V2
 
Last edited:
Upvote 0
OK, lets assume your formula was updated and uses the correct sheet name in both, if that is the case then this means the formula is not finding was in in W3 in your lookup list. So, either the value in W3 is different or the value in the lookup list is different. It could be a extra space on the end causing the problem.

Can you do a =Len(w3) and also a Len(?#) where the ?# is equal to the matching cell in your lookup list that you expect W3 to match.
 
Upvote 0
philwojo said:
OK, lets assume your formula was updated and uses the correct sheet name in both, if that is the case then this means the formula is not finding was in in W3 in your lookup list. So, either the value in W3 is different or the value in the lookup list is different. It could be a extra space on the end causing the problem.

Can you do a =Len(w3) and also a Len(?#) where the ?# is equal to the matching cell in your lookup list that you expect W3 to match.
Click to expand...

Okay, so I went into the column where the initial exchange number is (W2) and used the delete key several times right after the number, then suddenly the VLOOKUP worked. That tells me the data column I am asking it to use as the search criteria has issues. What should I do to correct this column?
 
Upvote 0
It depends on what is right and what is wrong, only you can say that.

If it is your lookup column you can either clean it up on you own, or maybe try this updated formula:

Code: 
[COLOR=#333333]=VLOOKUP(clean(trim(W2)),'Engineering'!$C$2:$J$375,8,FALSE)[/COLOR]
 
Upvote 0
philwojo said:
It depends on what is right and what is wrong, only you can say that.

If it is your lookup column you can either clean it up on you own, or maybe try this updated formula:

Code: 
[COLOR=#333333]=VLOOKUP(clean(trim(W2)),'Engineering'!$C$2:$J$375,8,FALSE)[/COLOR]
Click to expand...

I tried the code above and it did not work. Definitely a non-visible character after the last number because I only have to use the delete key once to get the Vlookup to work. I have 20 k of lines of data so I really need to find a formula that will fix rather than trying to fix one line at a time.
 
Upvote 0
Did you do the =LEN formulas I suggested earlier?

I would be curious to see how many characters are in W2 compared to C# for whatever matches.
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,891
Messages
6,175,229
Members
452,621
Latest member
Laura_PinksBTHFT

Share this page

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