Matching - Comparing Serials between two sheets

[dragonmouse]

I have two worksheets: Both sheets have serials:

Sheet 1 Column B has serials from B2:B500
Sheet 2 Column E has serials from B2:4000

In column F Sheet 2. I want to compare all the serials on both sheets and IF it's a "match" I want it to return "ACTIVE" otherwise "NON-ACTIVE".

My essesence is I have a few thousand widgets we've worked. Some of these widgets are no longer in service. I need to compare between the two databases to investigate if those in service are up to date and those out of service have been destroyed Thank you in advance.

 

[Joe4]

You can use the MATCH function to see if a value is found in the other sheet, i.e.

=IF(ISNUMBER(MATCH(E2,Sheet1!B1:B4000,0)),"ACTIVE","NON-ACTIVE")

 

[BSALV]

Map1
	D	E	F	G	H	I	J	K
1		abc	Active		Sheet 1 Column B has serials from B2:B500
2		efg	Not Active		Sheet 2 Column E has serials from B2:4000
3
4
sheet2

Cell Formulas
Range	Formula
F1:F2	F1	=IF(ISNUMBER(MATCH(E1,sheet1!$B$1:$B$500,0)),"Active","Not Active")

 

[dragonmouse]

Hmmm...Neither are quite working. Maybe I'll try switching around.

 

[Joe4]

Feel free to post your formula, if you would like us to review it.
Note that the values must match EXACTLY. Something as simple as an extra space will cause it not to match.
Also, they must be the same data type. You cannot match numbers to numbers entered as text, or dates to strings.

One way to check is to manually locate a match. Let say that you are on Sheet2, and the value in cell E4 visually matches the value in cell B29 on Sheet1.
Then enter this formula in some blank cell on Sheet2:

=E4=Sheet1!B29

if that does not return TRUE, then the value do not match (despite what they may look like).

 

[dragonmouse]

Forumula is:

=IF(ISNUMBER(MATCH(E5,Sheet2!B2:B500,0)),"Active","Not Active")

It's returning only Not Active. I tried the "Truth" forumula "
'=E110=Sheet2!$B$2 (I used the $B$2 because there were several lines on sheet 1 that had the same serial number). And it came out TRUE So the values should be good. I selected sheet1 cells E110 because I could visually see that 110, 111 and 112 all matched cell B2 on sheet2

​

 

[Joe4]

OK, what happens if you try the formula for that particular value, i.e.

=IF(ISNUMBER(MATCH(E110,Sheet2!B2:B500,0)),"Active","Not Active")

Actually, I think I have a pretty good idea where your problem is. I am guessing that you are entering this formula on the first row, and then copying it down.
If you do that, you need to be sure to "anchor" your lookup range, so it doesn't shift as the formula moves down the page.
It should look like:

=IF(ISNUMBER(MATCH(E5,Sheet2!B$2:B$500,0)),"Active","Not Active")

Note the "$" in front of the row number to lock our lookup range down.

 

[dragonmouse]

Oh heaven help me. I'll be soooooo glad when Excel just learns to read my mind .

That was it. I didn't "anchor" the Sheet2 range. THANK YOU. This is SOOOOOO going to speed up my research!

 

[Joe4]

You are welcome!
Glad we were able to help.

One "trick" that many people use to avoid that "shifting" range issue is to name their range, and then use the named range in the formula.
Then, when you copy the formula to other cells, you do not need to worry about the range changing.

So if we named the range Sheet2!B2:B500 something like "MyRange", we could then write:

=IF(ISNUMBER(MATCH(E5,MyRange,0)),"Active","Not Active")