I want to be able to enter a value (an ID#) on sheet 1 of workbook (say, A1)
and compare to a list of ID#'s on another sheet, returning one message if it
matches and another if it is not found.

I have tried Vlookup but does not seem to work when data entering a value.
Thoughts?

thanks!

list of ID#'s on Sheet2, A1:A100

=IF(ISNUMBER(MATCH(A1,'Sheet2'!A1:A100)),"Found ID","ID not found")
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"TKD" wrote:

I want to be able to enter a value (an ID#) on sheet 1 of workbook (say, A1)
and compare to a list of ID#'s on another sheet, returning one message if it
matches and another if it is not found.

I have tried Vlookup but does not seem to work when data entering a value.
Thoughts?

thanks!

Have tried that...but I will enter a value that happens to be the same string
length as my those in my list and it comes up as found, even though it is not
there...this is what is throwing me...thoughts?

Thanks!

"Luke M" wrote:

list of ID#'s on Sheet2, A1:A100

=IF(ISNUMBER(MATCH(A1,'Sheet2'!A1:A100)),"Found ID","ID not found")
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"TKD" wrote:

I want to be able to enter a value (an ID#) on sheet 1 of workbook (say, A1)
and compare to a list of ID#'s on another sheet, returning one message if it
matches and another if it is not found.

I have tried Vlookup but does not seem to work when data entering a value.
Thoughts?

thanks!

Hi,

=IF(ISNA(VLOOKUP(A1,Sheet2!A1:A1000,1,FALSE)),"not found","found")

Enter the value in A1

If it doesn't work it is most likely because the value in A1 is a number but
the identical item in Sheet2 is text.

Problem:
When numbers are enter as text they may not calculate within formulas as
they should. A few formulas will work fine despite the numbers being
entered as text. Numbers can be stored as text by 1. preformatting the cell
to Text and entering the number, 2. Typing an apostrophy in front of the
number '123, 3. Because the data was downloaded from a soure inwhich it was
stored as a text number, 4. Because you used the Text to Columns command and
converted it to text., and....

There is no sure indicator that a number is stored as text, although numbers
are usually right aligned and text left aligned, this may not be the case.
If you are using a later version of Excel, Error Checking green triangles
may appear at the top left corner of these cell, but this feature may be off
or the version of Excel may not support it. (2000 and earlier).

You can find out what data type the entries are by using the =ISTEXT(A1) or
=ISNUMBER(A1) functions. You can not tell by checking the Format. If a
number was entered in a cell preformatted as General or as a number, then it
will be a number, even if it's current format is Text. Likewise a number
entered in a cell preformatted as Text will be text even if it's current
format is Number, General, Date, Currency and the like.

Solution:
1. Change the format to one that is numeric and then reenter the numbers
(too slow and error prone.)
2. Select the cells and open the Error Checking options and choose Convert
to Numbers. (this is not available in 2000 or earlier)
3. Select an empty cell and copy it. Select the text number cell and choose
Edit, Paste Special, Add (or Subtract). This method is ~100 times faster
than #2.

Dates are numbers, and if they are stored as text, you will not get an Error
Checking triangle, so method #3 is obligatory if there is a substantial
number of dates to convert.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire

"TKD" wrote in message
...
I want to be able to enter a value (an ID#) on sheet 1 of workbook (say,
A1)
and compare to a list of ID#'s on another sheet, returning one message if
it
matches and another if it is not found.

I have tried Vlookup but does not seem to work when data entering a value.
Thoughts?

thanks!

Thank you all for the advice..we found another solution that seems to be
working.

We set up a COUNTIF function and based on the entered value assigned it
either a 0 or 1 based on if it shows up in the list. Based on the 0 or 1, we
can display the message we want. Probably not as sexy as other solutions, but
it seems to be working.

"Shane Devenshire" wrote:

Hi,

=IF(ISNA(VLOOKUP(A1,Sheet2!A1:A1000,1,FALSE)),"not found","found")

Enter the value in A1

If it doesn't work it is most likely because the value in A1 is a number but
the identical item in Sheet2 is text.

Problem:
When numbers are enter as text they may not calculate within formulas as
they should. A few formulas will work fine despite the numbers being
entered as text. Numbers can be stored as text by 1. preformatting the cell
to Text and entering the number, 2. Typing an apostrophy in front of the
number '123, 3. Because the data was downloaded from a soure inwhich it was
stored as a text number, 4. Because you used the Text to Columns command and
converted it to text., and....

There is no sure indicator that a number is stored as text, although numbers
are usually right aligned and text left aligned, this may not be the case.
If you are using a later version of Excel, Error Checking green triangles
may appear at the top left corner of these cell, but this feature may be off
or the version of Excel may not support it. (2000 and earlier).

You can find out what data type the entries are by using the =ISTEXT(A1) or
=ISNUMBER(A1) functions. You can not tell by checking the Format. If a
number was entered in a cell preformatted as General or as a number, then it
will be a number, even if it's current format is Text. Likewise a number
entered in a cell preformatted as Text will be text even if it's current
format is Number, General, Date, Currency and the like.

Solution:
1. Change the format to one that is numeric and then reenter the numbers
(too slow and error prone.)
2. Select the cells and open the Error Checking options and choose Convert
to Numbers. (this is not available in 2000 or earlier)
3. Select an empty cell and copy it. Select the text number cell and choose
Edit, Paste Special, Add (or Subtract). This method is ~100 times faster
than #2.

Dates are numbers, and if they are stored as text, you will not get an Error
Checking triangle, so method #3 is obligatory if there is a substantial
number of dates to convert.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire

"TKD" wrote in message
...
I want to be able to enter a value (an ID#) on sheet 1 of workbook (say,
A1)
and compare to a list of ID#'s on another sheet, returning one message if
it
matches and another if it is not found.

I have tried Vlookup but does not seem to work when data entering a value.
Thoughts?

thanks!