VLookup formula and #N/A

mad3

Board Regular
Joined
Sep 15, 2009
Messages
95
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!
Mgr
Exchange
Steve Mendoza
20374
#N/A
20372
20684
20684
20682
20682
20684
20686
50157
20684
20686
20374
70172
20374

<tbody>
</tbody>





Exchange
Office Name
Order code
Division
Engineering
Phone Number
OSP Engineering Manager
20372
Adelanto
ADLNCAXF
South
Oscar martinez
123-713-0123
Todd werth
20374
ADIN
North
Albert rose
713-123-1234
Steve Mendoza
20684
Alamitos
SLBHCAXF
West
Oscar martinez
614-213-1234
Ray tabler
20682
Alderpoint
ALPNCAXF
North
Albert rose
615-345-0987
Steve Mendoza
20684
Aliso (RSU)
SLGBCAXF
West
Oscar martinez
614-234-0123
Ray tabler
20686
Alondra
NRWLCAXG
West
Oscar martinez
657-000-1111
Ray tabler
70172
Alpaugh
ALPGCAXF
North
Albert rose
666-555-1111
Jon Welton
20374
Antelope
LNCSCAXF
South
Oscar martinez
111-222-3333
Todd werth
20374
Antelope Acres
LNCSCAXE
South
Oscar martinez
222-111-3131
Todd werth
20299
Anza
ANZACAXF
South
Oscar martinez
222-111-2211
Bill Karns
70173
Apple Valley
APVYCAXF
South
Oscar martinez
111-222-1122
Todd werth
20476
ARBUCKLE
North
Albert rose
111-333-1111
Steve Mendoza
20299
Arrowhead
ARHDCAXF
South
Oscar martinez
333-111-2222
Bill Karns
70001
Artesia
ARTSCAXF
West
Oscar martinez
444-111-2222
Ray tabler

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,451
Office Version
  1. 2010
Platform
  1. Windows
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:

philwojo

Well-known Member
Joined
May 10, 2013
Messages
533

ADVERTISEMENT

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.
 

mad3

Board Regular
Joined
Sep 15, 2009
Messages
95
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.

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?
 

philwojo

Well-known Member
Joined
May 10, 2013
Messages
533

ADVERTISEMENT

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]
 

mad3

Board Regular
Joined
Sep 15, 2009
Messages
95
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]

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.
 

philwojo

Well-known Member
Joined
May 10, 2013
Messages
533
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,974
Messages
5,525,991
Members
409,673
Latest member
Riseee

This Week's Hot Topics

Top