Check to See ( Find ) if a Value is in a Table

[Paul Black]

Hi Everyone,

I have a Sheet ( Named "Data" ) that has One Combination ( in the
Format 01-02-03-04-05-06 ) in Each Cell from A1:Z65000.
In Another Sheet ( Named "Draws" ) I have a Column ( Column "B" for
example ) with Several Hundred Draw Numbers in it.
What I would like to do is to Check if the Combination in Cell "B1" (
in the Sheet Named "Draws" ) is Anywhere in the "Data" Sheet. If it
is, it could put "Found", and if NOT put "Not Found" for example. Then
do the same for "B2","B3","B4" etc.
Is there a Formula that would achieve this or would it have to be done
using VB.

Thanks Very Much in Advance.
Paul

 

[Frank Kabel]

Hi
try the following formula 8note: this will probably slow Excel down due
to your large range -> you may turn off automatic calculation):
=IF(COUNTIF('data'!$A$1:$Z$65000,B1)>0,"Found","Not found")
and copy this down

 

[Paul Black]

Hi Frank,

Thanks very much for the Formula, it works Great.
You are quite right though, it does Slow down Excel, even to the point
that I actually thought it had Stopped. Would it be Better to have a
Macro to get the Results and maybe list them in Column "D" for
example.

Thanks
Paul

 

[Frank Kabel]

Hi Paul
you may try the following:
- first disable automatic calculation
- insert the formulas (copy them)
- re-calculate (hit F9)
- copy this column and goto 'Edit - Paste Special' and choose 'Values'
(to remove the formulas)
- enable automatic calculation.

This time you only have to wait once for the re-calculation