software

Excel 2000

Really simple data sheet: list of names with homerooms
Lookup table: list of homerooms with teachers. (named quot;Rangequot;)

=VLOOKUP(E2,Range,2)

Some of the cells return the correct name, others #N/A.

As far as I can tell they are all formatted exactly the same; I've tried
several different formatting options. The correct ones stay the same; the no
data ones do not change either.

Any ideas?
Did you check for extra blanks at the end of your values?

HTH
--
AP

quot;amberlodgequot; gt; a écrit dans le message
de ...
gt; Excel 2000
gt;
gt; Really simple data sheet: list of names with homerooms
gt; Lookup table: list of homerooms with teachers. (named quot;Rangequot;)
gt;
gt; =VLOOKUP(E2,Range,2)
gt;
gt; Some of the cells return the correct name, others #N/A.
gt;
gt; As far as I can tell they are all formatted exactly the same; I've tried
gt; several different formatting options. The correct ones stay the same; the
no
gt; data ones do not change either.
gt;
gt; Any ideas?
gt;
gt;
The usual suspects:

Leading or trailing spaces in the lookup column.
Does the named range contain ALL the data?
Use the 4th argument as False or 0

=VLOOKUP(E2,Range,2,0)

HTH
Regards,
Howard

quot;amberlodgequot; gt; wrote in message
...
gt; Excel 2000
gt;
gt; Really simple data sheet: list of names with homerooms
gt; Lookup table: list of homerooms with teachers. (named quot;Rangequot;)
gt;
gt; =VLOOKUP(E2,Range,2)
gt;
gt; Some of the cells return the correct name, others #N/A.
gt;
gt; As far as I can tell they are all formatted exactly the same; I've tried
gt; several different formatting options. The correct ones stay the same; the
gt; no
gt; data ones do not change either.
gt;
gt; Any ideas?
gt;
gt;
I just did and could find no extra spaces or blanks. Several names have the
same homeroom; with some the formula works, with others it doesn't. If I
paste the lookup value that works into the ones that don't, the formula
works. Yet the cells were identical. I tried typing the number in from
scratch and that did not work.

quot;Ardus Petusquot; wrote:

gt; Did you check for extra blanks at the end of your values?
gt;
gt; HTH
gt; --
gt; AP
gt;
gt; quot;amberlodgequot; gt; a écrit dans le message
gt; de ...
gt; gt; Excel 2000
gt; gt;
gt; gt; Really simple data sheet: list of names with homerooms
gt; gt; Lookup table: list of homerooms with teachers. (named quot;Rangequot;)
gt; gt;
gt; gt; =VLOOKUP(E2,Range,2)
gt; gt;
gt; gt; Some of the cells return the correct name, others #N/A.
gt; gt;
gt; gt; As far as I can tell they are all formatted exactly the same; I've tried
gt; gt; several different formatting options. The correct ones stay the same; the
gt; no
gt; gt; data ones do not change either.
gt; gt;
gt; gt; Any ideas?
gt; gt;
gt; gt;
gt;
gt;
gt;

I checked for spaces - there were none that I could find. I tried typing in
the data from scratch - no good. Copying and pasting from those cells that
did work into the (identical) cells that didn't, caused the formula to return
the correct value. But I still have homerooms that have never given a correct
value. Even true, false, and 0 don't help in the fourth argument.

The range contains all the data - it's not very big - just 44 rows.

quot;L. Howard Kittlequot; wrote:

gt; The usual suspects:
gt;
gt; Leading or trailing spaces in the lookup column.
gt; Does the named range contain ALL the data?
gt; Use the 4th argument as False or 0
gt;
gt; =VLOOKUP(E2,Range,2,0)
gt;
gt; HTH
gt; Regards,
gt; Howard
gt;
gt; quot;amberlodgequot; gt; wrote in message
gt; ...
gt; gt; Excel 2000
gt; gt;
gt; gt; Really simple data sheet: list of names with homerooms
gt; gt; Lookup table: list of homerooms with teachers. (named quot;Rangequot;)
gt; gt;
gt; gt; =VLOOKUP(E2,Range,2)
gt; gt;
gt; gt; Some of the cells return the correct name, others #N/A.
gt; gt;
gt; gt; As far as I can tell they are all formatted exactly the same; I've tried
gt; gt; several different formatting options. The correct ones stay the same; the
gt; gt; no
gt; gt; data ones do not change either.
gt; gt;
gt; gt; Any ideas?
gt; gt;
gt; gt;
gt;
gt;
gt;

Change the lookup formula to=VLOOKUP(E2,Range,2,0)

if you still have the same problem and if there are no invisible characters
then there must be numbers involved where one set are seen as number and one
as text, just do this

=ISTEXT(Cell1)

=ISTEXT(Cell2)

where the first one is the lookup value cell and the second one cell in the
table that returns an error

if you get TRUE in one of these (probably the second) copy an empty cell,
select all the room numbers
and do editgt;paste special and select add

--

Regards,

Peo Sjoblom

nwexcelsolutions.comquot;amberlodgequot; gt; wrote in message
...
gt;I just did and could find no extra spaces or blanks. Several names have
gt;the
gt; same homeroom; with some the formula works, with others it doesn't. If I
gt; paste the lookup value that works into the ones that don't, the formula
gt; works. Yet the cells were identical. I tried typing the number in from
gt; scratch and that did not work.
gt;
gt; quot;Ardus Petusquot; wrote:
gt;
gt;gt; Did you check for extra blanks at the end of your values?
gt;gt;
gt;gt; HTH
gt;gt; --
gt;gt; AP
gt;gt;
gt;gt; quot;amberlodgequot; gt; a écrit dans le
gt;gt; message
gt;gt; de ...
gt;gt; gt; Excel 2000
gt;gt; gt;
gt;gt; gt; Really simple data sheet: list of names with homerooms
gt;gt; gt; Lookup table: list of homerooms with teachers. (named quot;Rangequot;)
gt;gt; gt;
gt;gt; gt; =VLOOKUP(E2,Range,2)
gt;gt; gt;
gt;gt; gt; Some of the cells return the correct name, others #N/A.
gt;gt; gt;
gt;gt; gt; As far as I can tell they are all formatted exactly the same; I've
gt;gt; gt; tried
gt;gt; gt; several different formatting options. The correct ones stay the same;
gt;gt; gt; the
gt;gt; no
gt;gt; gt; data ones do not change either.
gt;gt; gt;
gt;gt; gt; Any ideas?
gt;gt; gt;
gt;gt; gt;
gt;gt;
gt;gt;
gt;gt;
Thanks for your replies. I did try 0 in the fourth argument. No good. But I
was checking for spaces and clicked in front of several of the homeroom
values in my table, which caused them to be right-justified instead of left
(thus changing the format?) and the formulas that were working gave values of
#N/A. I left-justified them again and now the entire dtatbase is fine. I have
no idea what I did to clear up the problem, but I all the variations of the
formula that I was trying work now.

quot;Peo Sjoblomquot; wrote:

gt; Change the lookup formula to
gt;
gt;
gt; =VLOOKUP(E2,Range,2,0)
gt;
gt; if you still have the same problem and if there are no invisible characters
gt; then there must be numbers involved where one set are seen as number and one
gt; as text, just do this
gt;
gt; =ISTEXT(Cell1)
gt;
gt; =ISTEXT(Cell2)
gt;
gt; where the first one is the lookup value cell and the second one cell in the
gt; table that returns an error
gt;
gt; if you get TRUE in one of these (probably the second) copy an empty cell,
gt; select all the room numbers
gt; and do editgt;paste special and select add
gt;
gt;
gt;
gt;
gt; --
gt;
gt; Regards,
gt;
gt; Peo Sjoblom
gt;
gt; nwexcelsolutions.com
gt;
gt;
gt; quot;amberlodgequot; gt; wrote in message
gt; ...
gt; gt;I just did and could find no extra spaces or blanks. Several names have
gt; gt;the
gt; gt; same homeroom; with some the formula works, with others it doesn't. If I
gt; gt; paste the lookup value that works into the ones that don't, the formula
gt; gt; works. Yet the cells were identical. I tried typing the number in from
gt; gt; scratch and that did not work.
gt; gt;
gt; gt; quot;Ardus Petusquot; wrote:
gt; gt;
gt; gt;gt; Did you check for extra blanks at the end of your values?
gt; gt;gt;
gt; gt;gt; HTH
gt; gt;gt; --
gt; gt;gt; AP
gt; gt;gt;
gt; gt;gt; quot;amberlodgequot; gt; a écrit dans le
gt; gt;gt; message
gt; gt;gt; de ...
gt; gt;gt; gt; Excel 2000
gt; gt;gt; gt;
gt; gt;gt; gt; Really simple data sheet: list of names with homerooms
gt; gt;gt; gt; Lookup table: list of homerooms with teachers. (named quot;Rangequot;)
gt; gt;gt; gt;
gt; gt;gt; gt; =VLOOKUP(E2,Range,2)
gt; gt;gt; gt;
gt; gt;gt; gt; Some of the cells return the correct name, others #N/A.
gt; gt;gt; gt;
gt; gt;gt; gt; As far as I can tell they are all formatted exactly the same; I've
gt; gt;gt; gt; tried
gt; gt;gt; gt; several different formatting options. The correct ones stay the same;
gt; gt;gt; gt; the
gt; gt;gt; no
gt; gt;gt; gt; data ones do not change either.
gt; gt;gt; gt;
gt; gt;gt; gt; Any ideas?
gt; gt;gt; gt;
gt; gt;gt; gt;
gt; gt;gt;
gt; gt;gt;
gt; gt;gt;
gt;
gt;
gt;

After reading the messages back amp; forth I'm wondering if the name quot;Rangequot;
uses absolute or relative addresses. Go to Insertgt;Namesgt;Define and make sure
the definition has $ signs in the addresses

quot;amberlodgequot; wrote:

gt; Excel 2000
gt;
gt; Really simple data sheet: list of names with homerooms
gt; Lookup table: list of homerooms with teachers. (named quot;Rangequot;)
gt;
gt; =VLOOKUP(E2,Range,2)
gt;
gt; Some of the cells return the correct name, others #N/A.
gt;
gt; As far as I can tell they are all formatted exactly the same; I've tried
gt; several different formatting options. The correct ones stay the same; the no
gt; data ones do not change either.
gt;
gt; Any ideas?
gt;
gt;

If they are left aligned they are text

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Nothwest Excel Solutions
www.nwexcelsolutions.com
quot;It is a good thing to follow the first law of holes;
if you are in one stop digging.quot; Lord Healeyquot;amberlodgequot; gt; wrote in message
news
gt; Thanks for your replies. I did try 0 in the fourth argument. No good. But
gt; I
gt; was checking for spaces and clicked in front of several of the homeroom
gt; values in my table, which caused them to be right-justified instead of
gt; left
gt; (thus changing the format?) and the formulas that were working gave values
gt; of
gt; #N/A. I left-justified them again and now the entire dtatbase is fine. I
gt; have
gt; no idea what I did to clear up the problem, but I all the variations of
gt; the
gt; formula that I was trying work now.
gt;
gt; quot;Peo Sjoblomquot; wrote:
gt;
gt;gt; Change the lookup formula to
gt;gt;
gt;gt;
gt;gt; =VLOOKUP(E2,Range,2,0)
gt;gt;
gt;gt; if you still have the same problem and if there are no invisible
gt;gt; characters
gt;gt; then there must be numbers involved where one set are seen as number and
gt;gt; one
gt;gt; as text, just do this
gt;gt;
gt;gt; =ISTEXT(Cell1)
gt;gt;
gt;gt; =ISTEXT(Cell2)
gt;gt;
gt;gt; where the first one is the lookup value cell and the second one cell in
gt;gt; the
gt;gt; table that returns an error
gt;gt;
gt;gt; if you get TRUE in one of these (probably the second) copy an empty cell,
gt;gt; select all the room numbers
gt;gt; and do editgt;paste special and select add
gt;gt;
gt;gt;
gt;gt;
gt;gt;
gt;gt; --
gt;gt;
gt;gt; Regards,
gt;gt;
gt;gt; Peo Sjoblom
gt;gt;
gt;gt; nwexcelsolutions.com
gt;gt;
gt;gt;
gt;gt; quot;amberlodgequot; gt; wrote in message
gt;gt; ...
gt;gt; gt;I just did and could find no extra spaces or blanks. Several names have
gt;gt; gt;the
gt;gt; gt; same homeroom; with some the formula works, with others it doesn't. If
gt;gt; gt; I
gt;gt; gt; paste the lookup value that works into the ones that don't, the formula
gt;gt; gt; works. Yet the cells were identical. I tried typing the number in from
gt;gt; gt; scratch and that did not work.
gt;gt; gt;
gt;gt; gt; quot;Ardus Petusquot; wrote:
gt;gt; gt;
gt;gt; gt;gt; Did you check for extra blanks at the end of your values?
gt;gt; gt;gt;
gt;gt; gt;gt; HTH
gt;gt; gt;gt; --
gt;gt; gt;gt; AP
gt;gt; gt;gt;
gt;gt; gt;gt; quot;amberlodgequot; gt; a écrit dans le
gt;gt; gt;gt; message
gt;gt; gt;gt; de ...
gt;gt; gt;gt; gt; Excel 2000
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; Really simple data sheet: list of names with homerooms
gt;gt; gt;gt; gt; Lookup table: list of homerooms with teachers. (named quot;Rangequot;)
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; =VLOOKUP(E2,Range,2)
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; Some of the cells return the correct name, others #N/A.
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; As far as I can tell they are all formatted exactly the same; I've
gt;gt; gt;gt; gt; tried
gt;gt; gt;gt; gt; several different formatting options. The correct ones stay the
gt;gt; gt;gt; gt; same;
gt;gt; gt;gt; gt; the
gt;gt; gt;gt; no
gt;gt; gt;gt; gt; data ones do not change either.
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt; Any ideas?
gt;gt; gt;gt; gt;
gt;gt; gt;gt; gt;
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt;
gt;gt;
gt;gt;

Maybe this..

=VLOOKUP(E2,Range,2,FALSE)--
Infinity
------------------------------------------------------------------------
Infinity's Profile: www.excelforum.com/member.php...oamp;userid=32725
View this thread: www.excelforum.com/showthread...hreadid=532711