Solved Searching for multiple numbers within a data set

May 25, 2013 at 09:31:09
Specs: Windows Vista
In Excel, I have 2 sheets of data. One sheet contains a list of numbers (about 500 line items) . The other sheet (sheet 2) is imported and it conatins about (150 lines). I need excel to search sheet 1 with the numbers I have in sheet 2 and tell me if there are any matching numbers.

Anyone able to help?


See More: Searching for multiple numbers within a data set

Report •

#1
May 25, 2013 at 12:57:39
✔ Best Answer
Try this:

With your data in Sheet 1 in Column A starting at cell 1 to cell 500
and
With your data in Sheet 2 in Column A starting at cell 1 to cell 150

On Sheet 2 enter the formula cell B1:

=IF(ISERROR(MATCH(A1,Sheet1!$A$1:$A$500,0)),"No Match in Sheet 1","Match")

Drag down 150 rows.

If there is a Match, then the word Match will appear in column B
If there is no Match, then No Match in Sheet 1 will appear in column B

Also, since your importing the data, make sure your Numbers are really Numbers not TEXT that look like numbers.

Real numbers will be Right Justified in the cell.

A simply way to insure all your numbers are numbers is to do a simple: =A1*1 this should force any TEXT to Numbers.

See how that work.

Also, Excel questions are best asked in the Office Software Forum.

MIKE

http://www.skeptic.com/


Report •
Related Solutions


Ask Question