Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am having trouble with the vlookup function in excel.

I have 2 files: file1.xlsx and file2.xlsx. Both files have a list of products with a number of various information and I need to add values from file2 to products in file1 (paired based on the product name).

I have tried the following formula:

=VLOOKUP(B2, [file2.xlsx]Tovar!$A:$AR,38,FALSE)

but keep getting a value error.

The first value is the name of the product in file1, second value is the filename of file2, name of the sheet and the range which should be used for lookup and the third value is the number of the column from which data should be returned.

Is there something obvious that I am overlooking please? The value to be returned is a string.

share|improve this question

migrated from Feb 27 '13 at 17:04

This question came from our site for professional and enthusiast programmers.

VLOOKUP only returns a #VALUE! error when the third argument is negative - which is not the case here. Any chance your formula is more complex, e.g. you multiply the result of VLOOUP with a factor or so? In this case it might be that VLOOKUP returns a text?! – Peter Albert Feb 27 '13 at 10:42
What if you increase the AR to AS? – glh Feb 27 '13 at 12:30
Thank you for the advice, I have increased the range to AS, this unfortunately did not help. The exact returned value is #N/A - Value not available error. – Dusan Halas Feb 27 '13 at 13:16
VLOOKUP will also return a #VALUE error if the cell it returns contains a #VALUE error. Have you manually checked the cell it is looking at? – Dave Sexton Feb 27 '13 at 13:20
I have tried it on all the rows, hasn't worked with either. Now I actually tried the formula on a different set of files and it works. But does not work where I need it to. – Dusan Halas Feb 27 '13 at 13:51

If the error your are getting is #N/A then it could be that the two columns you are comparing contain different data type, i.e. text and a number. Try one of the following:

=VLOOKUP(TEXT(B2, "0"), [file2.xlsx]Tovar!$A:$AR,38,FALSE)

=VLOOKUP(VALUE(B2), [file2.xlsx]Tovar!$A:$AR,38,FALSE)
share|improve this answer
Thank you for all the advice, I simply recreated both files, wrote the function again, set all the values to text by default and it started working. I apologise for bothering you with this. – Dusan Halas Feb 27 '13 at 13:59

You must log in to answer this question.