vlookup problem showing #N/A

[s_himself]

Hi guys

I have created a workbook that is fetching a lot of data from different places in a datasheet with the help of vlookup formulas and some IF formulas. The document works for all my co-workers except for one who get "#N/A" in all the cells where there is a formula which should give a answer.

When I och my other co-workers download the file it works so it is not any problem with the lookup formula or the data sheet.. It must be some sort of setting in his excel that he accidently managed to change?

This file is stored in a Content management system, so we all download the same version of the file. But just this one co-worker gets this #N/A problem and I cant figure out why?

Anyone of you guys who experienced this problem before that could be our saviour for the day?

 

[RoryA]

Is the VLOOKUP formula using an exact match, or an approximate one? Also, what kind of data is it looking up: text, or numeric?

 

[s_himself]

Is the VLOOKUP formula using an exact match, or an approximate one? Also, what kind of data is it looking up: text, or numeric?

The cells is formated as General because it can be both numbers and text.
I have not choosen True or False, that option is empty...

The thing is that everything works for everybody else except on this specific colleagues computer.
We download the same version of the document at the same time from the same place.. On my computer and my other colleagues computers, it works like normal.. But not for this one colleague...

Therefore I dont think that it is anything wrong with the formula or the document itself, it has to be something with his excel settings?? This document has been working for years.. And it worked on his computer befor as well..

Any Ideas??

 

[RoryA]

If you don't specify that option, then it defaults to True which means the lookup table must be sorted by its first column. I can't think of any global Excel options which could prevent the formula from working for text and number values. The only things I can suggest to check are: the regional settings on the computer that doesn't work, whether Excel is set to use system separators in Excel options, and whether there are any add-ins installed on the problem computer that are not on the others. It may be worth testing Excel in safe mode on that computer (hold down the Ctrl key while starting Excel) to see if that makes a difference.

 

[Alex Blakenburg]

Just a random thought, are your vlookups acessing another workbook ?

 

[s_himself]

Just a random thought, are your vlookups acessing another workbook ?

Hi

No, it is just looking inside another sheet in the same workbook where the data is stored.

this is really strange how the same document can be working for all other users except just this one. We have still not been able to solve the problem and he needs to work in the document from time to time.

the short term solution is that he uses someone else computer or ask someone to do it for him, but in the long term that is not going to work

Any other ideas? Settings in excel that manages data, vlookup, calculations?

 

[Alex Blakenburg]

Did you try all Rory's suggestions ? ie
• Check regional settings are consistent (separators & dates)
• Disable all add-ins
• Open in safe mode
is the #N/A definitely the lookup not found #NA and not that the list used for the lookup contains an #NA (shouldn't vary between computers but would be good to know anyway)
Is the user perhaps opening web excel instead of the desktop application ?
Do his vlookups work in other workbooks ?
If you copy the summary sheet and lookup sheets simultaneously to the new workbook, does it work in the new workbook ?
Does he have calculations set to Auto ?

Apart from that I am out of ideas.

 

[s_himself]

Sorry guys, I totally missed Rory's reply.

I will have to check those suggestions. My colleague is back to the office next week, then I will try all these suggestions. Hopefully there is one of them that is the solution Im seeking.

The lookup table is always sorted by its first column and have always works from day 1 for everyone. Suddenly he started getting this #N/A problems. But I have actually not tried another workbook with vlookup formulas. That is the first thing I have to do.

My co-worker is not really in to computers that much. Ha has other interests, so probably he would not aware if he accidentally would make any global changes on his computer or excel that effected the outcome... But I told him that I will fix it, because I really like excel and he knows it (And I know that mrExcel exists)

Thank guys, I will try you suggestions and come back with the outcome

 

[s_himself]

Hi guys!

Finally I found it!

Thanks to you guys, I looked at the formulas and they were separated with comma and not semicolon.

Somehow, his global region settings had been changed to match windows language and not swedish.

Followed this step-by-step instructions: Comma or Semicolon in an Excel formula

Now it works like a charm

Thanks for helping me find the problem!

 

[Alex Blakenburg]

Thanks for coming back and closing the loop on this. Glad we were able to help.