VLookup returns N/A or wrong value

**vindieselgal** · 08-08-2011, 03:48 AM

hey there,
I am working on an excel sheet with around 21,000 rows and using VLOOKUP function to compare two worksheets and get a common value. I need to lookup the column C (Long code print format) in sheet one and compare it to sheet 2 to find the amount corresponding. The results are displayed in Sheet 1 column D. I have attached a dummy sheet whereby if i use the formula =VLOOKUP(C:C,Sheet2!A:C,3,FALSE) it works like a charm and gives me the correct amount, but if i implement it on the whole sheet, it gives me a N/A result.

I think its also worth noting that if i use TRUE in the function, it gives me a result that is a row above what is expected. What could be the reason behind that?

**Bob Phillips** · 08-08-2011, 04:06 AM

Why are you looking up C:C, not just C2?

**vindieselgal** · 08-08-2011, 04:28 AM

hey,

that is just one of the methods i used. but whether i use C2 or the whole column C:C i still get the same result. Like i said on this example, it works well, but when i try to implement on the 21,000 rows, well....thats where am still banging my head...

**Andrew-R** · 08-08-2011, 06:19 AM

Could it be that in your live data you have part numbers that are text and you're trying to match them against part numbers that are numeric? Even if the values look the same VLOOKUP doesn't treat them as the same.

You'd really have to show us an example of your problem.

**vindieselgal** · 08-08-2011, 06:51 AM

hey..
i think that is exactly the problem. i attached a dummy excel file with info for sheet1 and sheet2 at the beginning of this thread and as you can see, the data differs in all the columns. i have tried getting rid of trailing and leading spaces. Still no difference.
the boolean value FALSE returns a N/A while TRUE returns a value that is a row above the desired result... hope this clarifies my point.

**TMS** · 08-08-2011, 07:03 AM

=VLOOKUP(C2,Sheet2!A:C,3,FALSE) works fine for me. It does, however, return #N/A whenthe search value is not present.

I think you need to provide a more representative sample where the results are not as expected.

Regards

**arthurbr** · 08-08-2011, 07:04 AM

As far as your example goes, the results seem normal to me

**Andrew-R** · 08-08-2011, 07:04 AM

Yes, I saw the dummy example, but as the formula you're using works in that example it doesn't really show the problem.

Changing the FALSE to a TRUE tells Excel you're searching on sorted data and that it should return the nearest value, so it doesn't really help, sorry.

**vindieselgal** · 08-08-2011, 07:12 AM

i agree but it didnt work on the whole sheet. Good news is that i got it to work by getting rid of the leading, trailing and excessive spaces. For the green ones in Excel functions, ASAP Utilities are a Godsent and can be downloaded at http://www.asap-utilities.com/downlo...-utilities.php . here they got macros that can help perform these functions. thanks alot y'all for your help :-)

**TMS** · 08-08-2011, 07:20 AM

If your search data doesn't match the lookup table, you're not going to get matches.

You could also try:

=VLOOKUP(TRIM(C2)&"",Sheet2!A:C,3,FALSE)

Regards