VLOOKUP returns error when table array is from another sheet - Excel settings?

[zak1914]

Hi all

I encounter an issue with a specific excel file - I cannot use vlookup function (returns error) when the table array is on another sheet in the same workbook.

This happens for the same excel file both in my business and my personal laptops.

I tested the function in a new excel file in both laptops and works fine.

Does anyone have any idea about the source of this problem in the specific excel file?

Unfortunately I cannot share the file as it contains confidential data. But I attach an example which is working to show the formula I am using: VLOOKUP(A5,Sheet2!$F$9:$H$19,3,FALSE)

Any idea/advice will be much appreciated!

[Pete_UK]

Well, you could tell us what formula you are using ...

Pete

[zak1914]

Hi Pete - my bad, I have updated my initial post with a working example and the formula I am using : VLOOKUP(A5,Sheet2!$F$9:$H$19,3,FALSE)

[Pete_UK]

Well, that seems to be working okay in your sample file.

In what way is it not working in your real file? Are you getting incorrect results, or blanks, or errors etc.?

Pete

[zak1914]

I'm getting N/A error - value not available. This happens only when table array is in different sheet of the same workbook. If I transfer the table array on the same sheet then it works flawlessly.

[Pete_UK]

That error implies that the name has not been found in the table. Perhaps you have a leading or trailing space at the end of the names (or in A5 in your example), which means there is no exact match.

Hope this helps.

Pete

[zak1914]

I see Pete, I will have a look to and check again.

Update: Thanks a lot Pete - you lead me to the fault! A5 cells was in text format when table array in number. I haven't thought that this would be the error. Many thanks again!

[Pete_UK]

You're welcome, Zak - thanks for the rep.

Pete