VLookup error when comparing 2 columns

[ringrim78]

Hi all,

I am having some trouble doing a very basic VLOOKUP where Im comparing two columns.

My Formula is =VLOOKUP(A1|$B$1:$B$82786|1|FALSE) and copied down. I am just getting N/A errors and don't understand why. I am attaching a screenshot. Shouldn't line 16 have returned a value?

Any help would be greatly appreciated!

Excel Error.jpg

[TMS]

It's possible you have trailing spaces on one side or the other so the data appears the same but isn't.

It also looks as though column A may be formatted as text and column B is formatted as numeric but left aligned.

If that's the case, you could try:

Please Login or Register  to view this content.

[sandy666]

maybe use text2columns, set text for both then try vlookup or index/match again

text2columns:
- delimited
- next
- next
- text
- finish

text2columns works with single column selection so you need to select range in column A (t2c) then select range in column B (t2c)

if you change these columns to the real numbers you'll lost leading zeroes

your choice

0348660	9028	#N/A	#N/A		=VLOOKUP(A1,$B$1:$B$22,1,0)
0538440	9031	#N/A	#N/A
100380	9037	#N/A	#N/A		=INDEX($B$1:$B$22,MATCH(A1,$B$1:$B$22,0))
10343	9042	#N/A	#N/A
10580	9044	#N/A	#N/A
105920	9060	#N/A	#N/A
107480	9066	#N/A	#N/A
107680	9086	#N/A	#N/A
107970	9088	#N/A	#N/A
108760	9081	#N/A	#N/A
11061	9112	#N/A	#N/A
110920	9117	#N/A	#N/A
111510	9124	#N/A	#N/A
112040	9126	#N/A	#N/A
114590	9127	#N/A	#N/A
9134	9134	9134	9134
115820	9138	#N/A	#N/A
115990	9139	#N/A	#N/A
11631	9144	#N/A	#N/A
11756	9152	#N/A	#N/A
11772	9153	#N/A	#N/A
11800	9165	#N/A	#N/A
text	text	vlookup	index/match