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

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_arrayis in A1:A10.

- Let's say thelookup_valueis 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:

=A5=C1

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.

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.

=VLOOKUP(B2,building,2,FALSE)

Any further advise would be appreciated.

Thanks

Ask Your Question

Weekly Poll

Do you think Monopoly should update its pieces?

Discuss in The Lounge

Poll History