VLookup formula and #N/A

[mad3]

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>

 

[offthelip]

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

 

[mad3]

That is my bad...it is the same...

 

[offthelip]

What does that mean??

 

[philwojo]

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]

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]

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:

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

 

[mad3]

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:

[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]

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.