Searcing multiple numbers

K

Ken

I want to build or import a list of numbers, each with
specific information in a description field, then search
that list with another list of numbers.....and I want it
to be one step.

Scenario: I have a list of 10,000 numbers, each with
specific information. I have another list of 200
numbers. I need to know what numbers in the list of 200
are on the master list of 10,000 and the specific
information that follows each of those numbers that makes
a match.

Can someone point me in the right direction?
 
J

JulieD

Hi Ken

if you use the VLOOKUP statement it will tell you whether the number is in
the master list and the associated information for that number
e.g. Main list sheet2 A1:B10000 - lst column numbers, 2nd column description
field
other list to match with this on sheet1 A1:A200
in b1 of sheet 1 type
=IF(ISNA(VLOOKUP(A1,Sheet2!$A$1:$B$10000,2,0)),"Not
found",VLOOKUP(A1,Sheet2!$A$1:$B$10000,2,0))

this will return the words "not found" if the number isn't in the master
list and will return the information from column B of the list if the
information is found.

Cheers
JulieD
 
M

Max

Another option to play with ..

Assume the 10,000 numbers, with associated data are
in Sheet1, cols A to C, from row1 down

(The 10K numbers are in A1:A10000 and are assumed unique)

100 Data1 Data11
101 Data2 Data12
102 Data3 Data13
103 Data4 Data14
104 Data5 Data15
105 Data6 Data16
106 Data7 Data17
107 Data8 Data18
108 Data9 Data19
109 Data10 Data2
etc

In Sheet2
-----------
Say the other list of 200 numbers is in A1:A200

Select B1:B200
(i.e. a range to match that in col A)

Put in the formula bar:

=IF(ISERROR(SMALL(IF(COUNTIF(Sheet1!A1:A10000,A1:A200)=1,A1:A200),ROW())),""
,SMALL(IF(COUNTIF(Sheet1!A1:A10000,A1:A200)=1,A1:A200),ROW()))

Array-enter the formula with CTRL+SHIFT+ENTER
instead of just pressing ENTER

Col B will list the numbers in col A
which are found / match those within col A of Sheet1
(in ascending order)

Put in C1:
=IF(B1="","",OFFSET(Sheet1!$A$1,MATCH($B1,Sheet1!$A:$A,0)-1,COLUMN(A1)))
Copy C1 across to D1, fill down to D200

Cols C and D will extract the associated data from Sheet1
corresponding to the numbers extracted in col B

Adapt / extend to suit
 
K

Ken

Max,

Thanks for the info on posting.

I will likely have multiple columns of specific info for
each number, so thanks for bringing that up and
addressing that scenario!

Ken
 
M

Max

You're welcome !

But do hang around and monitor your original post awhile

There could be invaluable insights from others
which I'm sure you wouldn't want to miss out on <g>
 
Top