June 4, 2009 at 09:17:09
Specs: Windows 2000 professional
Hi, I'm doing a very simple VLOOKUP (because my knowledge is simple so it doesn't get more advanced!), however, when I do the simple VLOOKUP(cell,list,column,FALSE) formula, for the some of the cells it returns an #N/A value when I know that what it is looking for is there. For example, if I had a list of 1 to 10 and I ask it to look for 2 in a table where 2 is clearly present and return the relevant value, it is returning an #N/A when I can quite clearly see it should be returning a value.

However it is not returning a blanket #N/A, so in some cases it is finding what I am asking it to, but in others it is not, when I can't see any reason why it's not picking it out?

If anyone has any advice of how I need to get round this probably rather novice issue that'd be great!

Thanks for your help

See More: VLOOKUP query

Report •

June 4, 2009 at 10:21:49
While your question is quite clear, there's not much help we can offer since we can't see your spreadsheet or formula from here.

At a minimum, post your formula so we can see the actual construction.

The one thing I can suggest is a quick test to see if the value you are looking up is really in the list (the lookup_array).

- Let's say your lookup_array is in A1:A10.
- Let's say the lookup_value is in C1.
- Let's say you see a 2 in A5, but your VLOOKUP returns #N/A when you put a 2 in C1.

Try this:

With the 2 in C1, enter this in an empty cell:


If they both contain a 2, this will return TRUE. If you get a FALSE, the 2 in A5 probably just looks like a 2.

Report •

June 8, 2009 at 00:57:44
Hi there

Thanks for getting back to me.

I tried the test and picked two cells (where I'm currently getting an #N/A value when I do the VLOOKUP)that had the same value in, and it does state TRUE, so I'm not sure where to head from here.

I'm a bit sensitive about posting the spreadsheet itself as it's not my data to be posting, but the formula is constructed as follows, where "building" is the name of the table it is looking in.


Any further advise would be appreciated.


Report •

Related Solutions

Ask Question