formatted as numbers, zero decimal spaces. is it because the numbers i'm
looking up are results of formulas? if so, then i am outta luck because i
pretty much have to use the formula. the original list has a 3 digit
constant prefix before the 5 digit number, the only problem is that sometimes
there is a space between the constant and the number and sometimes there is
not; and in the table that i am looking up the numbers in, there is always a
space between the constant and the number; therefore if i use the lookup
function with the original list, i won't get accurate results for the entries
that don't have a space.
--
nate


"Dave F" wrote:

Are the numbers in the cell references formatted as numbers or text? If
they're formatted as text your formula will not work.
--
Brevity is the soul of wit.


"nkc_esquire" wrote:

it works if i TYPE the number in the formula, instead of using the cell
reference. like: vlookup(73061,Sheet2!A$2:H$2000,8,FALSE) instead of
vlookup(Z17,Sheet2!A$2:H$2000,8,FALSE)
i wonder why. this really doesn't help me since i would have to type the
number that i want to look up in every single formula--that wouldn't be any
different than using ctrl-f...
--
nate


"Dave F" wrote:

It shouldn't matter that the table is on another worksheet in the same
workbook. You may want to take a look here for some hints as to how to get
VLOOKUP to work correctly:
http://www.techonthenet.com/excel/formulas/vlookup.php . There are many
other links relating to VLOOKUP he
http://www.google.com/search?hl=en&q=vlookup+excel

Take note of how the lookup table has to be structured. If you can't alter
the structure of your lookup table to conform to VLOOKUP's requirements, it
is likely possible that you can achieve the same thing with a combination of
the INDEX/MATCH functions. If you determine that you can't use VLOOKUP then
the best thing to do would be to respond with an explanation of how your
lookup table is structured.

Dave
--
Brevity is the soul of wit.


"nkc_esquire" wrote:

ok, i used the formula that you suggested, which sounded logical to me, but
it only appeared to work. Only, it returned all blanks, even in the cells
that I knew it should have returned a value.

i am using excel 2003 on microsoft xp.

does it matter that the table that i'm looking up is in another worksheet
within the same workbook? i don't think it should, but i'm just trying to
think of what i'm doing wrong.

i'll play around with it and dream of days when i get 2007...

thanks dave




--
nate


"Dave F" wrote:

Not sure why you would use an asterisk.

Use: =IF(ISERROR(VLOOKUP(vlookupcriteria)),0,1)

To your second point: it sounds like you are looking for an EXACT match, not
an approximate match, and if that exact match is found you want it returned.
Therefore, your VLOOKUP function should look something like this:

=IF(ISERROR(VLOOKUP(A1,B1:C10,1,FALSE)),"",VLOOKUP (A1,B1:C10,1,FALSE))

This basically tells Excel: if the VLOOKUP does not return an EXACT match,
return "" (instead of the #NA! error), else do the VLOOKUP and return the
exact match. This is made simpler in XL 2007 with the new IFERROR function
but my guess is you're not using that version of XL.

Does this get at what you're looking for?

Dave
--
Brevity is the soul of wit.


"nkc_esquire" wrote:

Excel wants to put an asterisk in the formula...see below

=IF(ISERROR(VLOOKUP(vlookupcriteria))*0,1)

that makes the formula work, but it returns true/false instead of 0/1, i
also checked enough of the records by hand to know a few of them are there,
ie the formula should return "true", and the formula failed--all cells
returned "false"

But...
lets say that i actually do use the vlookup function to look up a value in
another column. the column i'm trying to search in contains a list of five
digit numbers. if the number that i am searching for IS in that column, i
want the Col_index_num value (which is a date) to be returned. well, that
works fine for the numbers that ARE there, but the numbers that are NOT
there, the formula still returns a date, only the date that it returns is in
the row directly above where the number SHOULD BE. Therefore, my output is a
bunch of dates, which is what i want, only i don't know which dates are
correct (actually belong to a number that was found) and which dates are
invalid. does this make sense? its pretty difficult to explain...

--
nate


"Dave F" wrote:

Well, you can use VLOOKUP to return a binary code: 0 if VLOOKUP returns an
error, 1 if it finds the value. =IF(ISERROR(VLOOKUP([vlookup criteria]),0,1)

"If the VLOOKUP returns an error, then record 0, else record 1."

Dave
--
Brevity is the soul of wit.


"nkc_esquire" wrote:

how can i search a very long list of values to see if each individual value
in a shorter list is in the long list? essentially, i am trying to prevent
having to "ctrl-f" a thousand times...i don't need to return a value, like
vlookup does, i just need to see if the numbers in one list are in the other
list

thanks,
--
nate