Hi all,

Having some trouble with a simple VLOOKUP and I've no idea why. I've done this a million times and never had this before.

This is the formula...

=VLOOKUP(A3,[PalActive]PalActive!$A$2:$H$206,3,FALSE)

A2 is a School Name and Column 3 is a 3-5 digit ID number.

I've checked the formatting and they are both the same. Have tried the, as a numerical cells, general & text but still getting the same #N/A value.

After a manual check I know that the value 599 SHOULD be returned.


Any help greatly appreciated.

Thanks.

"Jay07" wrote:
Subject: VLOOKUP returning #N/A ??
This is the formula...
=VLOOKUP(A3,[PalActive]PalActive!$A$2:$H$206,3,FALSE)

A2 is a School Name and Column 3 is a 3-5 digit ID number.
I've checked the formatting and they are both the same.
Have tried the, as a numerical cells, general & text but
still getting the same #N/A value.
After a manual check I know that the value 599 SHOULD be
returned.

VLOOKUP returns a #N/A error because the lookup fails, not because of any
issue with column 3 of the lookup table.

Manually find what you think should match the contents of A3. Suppose it is
[PalActive]PalActive!$A$100. What does the following formula return?

=A3=[PalActive]PalActive!$A$100

If it returns FALSE as expected, start looking for reasons.

It is unclear from your comments what A3 and [PalActive]PalActive!$A$100
might contain.

If A3 is a school name (not A2 [sic]), look for differences in the number
and placement of spaces. To begin with, try:

=TRIM(SUBSTITUTE(A3,CHAR(160),""))=TRIM(SUBSTITUTE ([PalActive]PalActive!$A$100,CHAR(160),""))

The theory is that some of the spaces might be HTML non-breaking spaces
(&NBSP) and/or there are leading or trailing spaces.

Note that the suggestions above are for diagnostic purposes to help identify
the problem. They are not intended to be solutions.

If you still cannot find the problem, I suggest that you upload an example
Excel file (devoid of any private data) that demonstrates the problem to a
file-sharing website.

Then post the "shared", "public" or "view-only" link (aka URL; http://...)
in a response here. The following is a list of some free file-sharing
websites; or use your own.

Box.Net: http://www.box.net/files
Windows Live Skydrive: http://skydrive.live.com
MediaFi http://www.mediafire.com
FileFactory: http://www.filefactory.com
FileSavr: http://www.filesavr.com
RapidSha http://www.rapidshare.com

PS.... I wrote:
"Jay07" wrote:
Subject: VLOOKUP returning #N/A ??
This is the formula...
=VLOOKUP(A3,[PalActive]PalActive!$A$2:$H$206,3,FALSE)
[....]
I've checked the formatting and they are both the same.
[....]
Manually find what you think should match the contents of A3.
Suppose it is [PalActive]PalActive!$A$100.

The formats of A3 and [PalActive]PalActive!$A$2:$A$206 do not matter.

What does matter is that they are both type text or they are both type
numeric.

A cell can be formatted as numeric, but contain text, and vice versa. If A3
contains numeric 123, and [PalActive]PalActive!$A$100 contains text 123,
they will not match, notwithstanding the same cell format.

An additional test a

=ISTEXT(A3)=ISTEXT([PalActive]PalActive!$A$100)

That should return TRUE. If it returns FALSE, the problem is not formatting
per se, but a mismatch of the type of data, despite appearances and in spite
of formatting.

And does the worksheet PalActive still exist in the workbook PalActive?

Perhaps you changed the spelling slightly, for example by adding spaces.

(Although that usually results in a #REF error, it can result in a #N/A in
rare circumstances. On the other hand, admittedly, those circumstances are
not reflected in the form of your table reference, namely "[PalActive]"
instead a full pathname.)

What columns are the lookup value and the desired results in? If they are in columns A and C respectively then perhaps an example file would be helpful.