Countifs Function Help Needed

[LearnExcl]

Good morning Excel Gurus!

I have an updated name roster listed under on B1:B240 on Sheet1. I also have a database of names on B1:B240, C1:C240 and D1:D240 on Sheet2. I would like to crosscheck the database against the updated roster to see if there are any new personnel additions. In other words, I want to see if what I am showing matches with the updated roster. In this manner, I would like to construct a formula on C1 on Sheet2 to say:
If any of the names on B1:B240, C1:C240 and D1:D240 on Sheet2 have an exact match with the names appearing on B1:B240 on Sheet1, then give me that name, if not, give me nothing ("").

I have tried the countifs function, but had difficulty returning the names I wanted. Any help will be greatly appreciated.

 

[Akashwani]

Hi,

Does this work for you?

Excel Workbook
	B	C	D
1	Name1	Name2	Name3
2	Col	Colin	Coleen
3	Cam	Dex	Den
4	joe	Jo	Joey
5	Bill	Billy	Will
6	Bob	Bobby	Rob
7	Ben	fred	Ted
8	Jim	Tim	Tom
Sheet2

Excel Workbook
	B	C
1	Name	Match
2	Bill	Bill
3	Bob	Bob
4	Ak	*
5	Terry	*
6	Jo	Jo
Sheet1

You will need to change the cell references to suit yours.

=IF(COUNTIF(Sheet2!$B$1:$D$240,B1)>0,B1,"")

I hope that works.

Ak

 

[LearnExcl]

Hi Ak,

Thank you for your reply. However, I believe I was not able to clearly explain what I needed. In your example, I am actually crosschecking Sheet2 against Sheet1. In this case, what I need is, if any one of the names appearing on Sheet2 have an exact match with any one of the names appearing on Sheet1, then give me nothing, but if not, give me that name.
So, the formula I am trying to use is =IF(COUNTIF($B$1:$B$240,
'Sheet2'B1:D240)>0,"",'Sheet2'B1:D240).
Now, the problem with this formula is that I have criteria covering multiple cells, thus returning me the "#VALUE!" error. I just could not discover how to construct a formula to cite multiple criteria. Maybe I should use a different function instead.
Appreciate your help on this.

 

[Aladin Akyurek]

Good morning Excel Gurus!

I have an updated name roster listed under on B1:B240 on Sheet1. I also have a database of names on B1:B240, C1:C240 and D1:D240 on Sheet2. I would like to crosscheck the database against the updated roster to see if there are any new personnel additions. In other words, I want to see if what I am showing matches with the updated roster. In this manner, I would like to construct a formula on C1 on Sheet2 to say:
If any of the names on B1:B240, C1:C240 and D1:D240 on Sheet2 have an exact match with the names appearing on B1:B240 on Sheet1, then give me that name, if not, give me nothing ("").

I have tried the countifs function, but had difficulty returning the names I wanted. Any help will be greatly appreciated.

Sheet2

E1, enter and copy down:

=LOOKUP(9.99999999999999E+307,1/CHOOSE({1,2,3},
    ISNA(MATCH(B1,Sheet1!$B$1:$B$240,0)),
    ISNA(MATCH(C1,Sheet1!$B$1:$B$240,0)),
    ISNA(MATCH(D1,Sheet1!$B$1:$B$240,0))),B1:D1)

 

[LearnExcl]

Hi Aladin,

I tried your formula, but did not get what I wanted. I am placing this formula on Sheet1, so I am just wondering how is this formula going to crosscheck against the values on Sheet2 without any reference to it? Could that be the problem you think? I also have headers for all three columns under Sheet2 as well as the column on Sheet1.

 

[Aladin Akyurek]

Hi Aladin,

I tried your formula, but did not get what I wanted. I am placing this formula on Sheet1, so I am just wondering how is this formula going to crosscheck against the values on Sheet2 without any reference to it? Could that be the problem you think? I also have headers for all three columns under Sheet2 as well as the column on Sheet1.

The formula goes in E1 on Sheet2, not on Sheet1.

 

[Aladin Akyurek]

Hi Aladin,

I tried your formula, but did not get what I wanted. I am placing this formula on Sheet1, so I am just wondering how is this formula going to crosscheck against the values on Sheet2 without any reference to it? Could that be the problem you think? I also have headers for all three columns under Sheet2 as well as the column on Sheet1.

Let's recap. Your original post is not very clear.
Condider the following samples:

Sheet1, B1:B9, with B1 housing a header...

<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=64><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19 width=64>List1</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>FAD</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>DAD</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>LAD</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>KAD</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>VAD</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>XAD</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>GAG</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>PAD</TD></TR></TBODY></TABLE>

Sheet2, B1:D5, with B1:D1 housing headers...

<TABLE style="WIDTH: 144pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=192><COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19 width=64>Blist</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=64>Clist</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=64>Dlist</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>JAD</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>LAD</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>KAD</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>FAD</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>DAD</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>NAD</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>FAD</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>LAD</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>DAD</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19>JAD</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>VAD</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>XAD</TD></TR></TBODY></TABLE>

Given above, care to specify what desired results are?

 

[LearnExcl]

That's exactly right. The only difference is that my cell references are different and I have other data in between column B and C, and column C and D under Sheet 2. I will, though, need to place the formula on Sheet1.
By the way, I cannot thank you enough for taking time to help me with my issue - it is certainly very much appreciated.

 

[Aladin Akyurek]

That's exactly right. The only difference is that my cell references are different and I have other data in between column B and C, and column C and D under Sheet 2. I will, though, need to place the formula on Sheet1.
By the way, I cannot thank you enough for taking time to help me with my issue - it is certainly very much appreciated.

What must the formula on Sheet1 bring about given the above samples?

 

[LearnExcl]

Consider I have other information on Column C and E and the name columns are actually B1:B9, D1:D9 and F1:F9 with B1, D1 and F1 housing headers on Sheet2. And the info on Sheet1 stays the same.

I need to place the formula in C2 on Sheet1 to say:

If any of the names appearing in B2:B9, D2:D9 and F2:F9 on Sheet2 have an exact match with any of the names on Sheet1 (Column B2:B9), then give me nothing, but if not, give me that name.

I hope it makes sense. Please let me know if you need any further info.