If Statement to compare multiple fields

[nancybrown]

I have two separate spreadsheets that I need to compare against each other. One spreadsheet is our client's report that shows client name (A2:A100), Start Date (B2:B100), End Date (C2:C100). Spreadsheet two is ours and contains the same fields.

I need to compare our spreadsheet against the client's spreadsheet to insure all records in the clients spreadsheet are accounted for in our spreadsheet.

I've consolidated both spreadsheets into one workbook on the same tab.


Client: Column A2:A100 (name) AND Column B2:B100 (Start Date) AND Column C2:C100 (End Date)
Ours: Column E2:E100 (name) AND Column F2:F100 (Start Date) AND Column G2:G100 (End Date)

If all conditions match "Yes","No"

Can someone provide an IF Statement please?

 

[JoeMo]

Assuming no extra spaces, strange characters, etc. in either your data or the clients. In H2 entered as an array formula (confirm using shift+ctrl+enter not just enter) and copy down to cover your data:

=IF(ISNA(MATCH(E2&F2&G2,$A$2:$A$10&$B$2:$B$10&$C$2:$C$10,0)),"No","Yes")

 

[Akashwani]

Hi,

Does this help?.....

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	Name	Start Date	End Date		Name	Start Date	End Date		Result1	Result2
2	Bill	01/03/2013	08/03/2013		Billy	01/03/2013	08/03/2013		No	No
3	Bob	02/03/2013	09/03/2013		Bob	02/03/2013	09/03/2013		Yes	Yes
4	Ben	03/03/2013	10/03/2013		Benny	03/03/2013	10/03/2013		No	No
5	Dan	04/03/2013	11/03/2013		Dan	04/03/2013	11/03/2013		Yes	Yes
6	Den	05/03/2013	12/03/2013		Den	05/03/2013	12/03/2013		Yes	Yes
7	Don	06/03/2013	13/03/2013		Donny	06/03/2013	13/03/2013		No	No
8	Jim	07/03/2013	14/03/2013		Jim	07/03/2013	14/03/2013		Yes	Yes
9	Jon	08/03/2013	15/03/2013		Jon	08/03/2013	15/03/2013		Yes	Yes
10	Jed	09/03/2013	16/03/2013		Jed	09/03/2013	16/03/2013		Yes	Yes
11	Ted	10/03/2013	17/03/2013		Ted	10/03/2013	17/03/2013		Yes	Yes
12	Tom	11/03/2013	18/03/2013		Tommy	11/03/2013	18/03/2013		No	No
13	Tim	12/03/2013	19/03/2013		Tim	12/03/2013	19/03/2013		Yes	Yes
14
Sheet2

The formulas need to be copied down and you can choose which one you prefer.

I hope that works.

Ak

 

[nancybrown]

Hi Joe, I moved flields around slightly . . . This is the last row of code, each row is returning results as Yes even when they don't match and when columns A through C are blank.

=IF(ISNA(MATCH(E51&F51&G51,$A$2:$A$100&$B$2:$B$100&$C$2:$C$100,0)),"No","Yes")

 

[nancybrown]

I do have this code that compares one column against another, I'm not sure if it can be modified to do multiple fields or not.

=IF(E25="","",_xlfn.IFERROR(VLOOKUP(E25,$T$4:$T$1487,1,0),"No"))

 

[JoeMo]

Hi Joe, I moved flields around slightly . . . This is the last row of code, each row is returning results as Yes even when they don't match and when columns A through C are blank.

=IF(ISNA(MATCH(E51&F51&G51,$A$2:$A$100&$B$2:$B$100&$C$2:$C$100,0)),"No","Yes")

Sounds like you did not enter the formula as an array formula. If you enter it as an array formula, after the entry you should see curly braces around it in the formula bar like this:

{=IF(ISNA(MATCH(E51&F51&G51,$A$2:$A$100&$B$2:$B$100&$C$2:$C$100,0)),"No","Yes")}

Read my first post again for directions on entering an array formula.

 

[nancybrown]

Sorry Joe, not sure how to do this . . . If I enter the formula as you have done with {} the results return as all "No"

 

[Akashwani]

Hi,

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1	Name	Start Date	End Date
2	Bill	01/03/2013	08/03/2013
3	Bob	02/03/2013	09/03/2013
4	Ben	03/03/2013	10/03/2013
5	Dan	04/03/2013	11/03/2013
6	Den	05/03/2013	12/03/2013
7	Don	06/03/2013	13/03/2013
8	Jim	07/03/2013	14/03/2013
9	Jon	08/03/2013	15/03/2013
10	Jed	09/03/2013	16/03/2013
11	Ted	10/03/2013	17/03/2013
12	Tom	11/03/2013	18/03/2013
13	Tim	12/03/2013	19/03/2013
14
50					Name	Start Date	End Date		Result1	Result2	Result3
51					Billy	01/03/2013	08/03/2013		No	No	No
52					Bob	02/03/2013	09/03/2013		Yes	Yes	Yes
53					Benny	03/03/2013	10/03/2013		No	No	No
54					Dan	04/03/2013	11/03/2013		Yes	Yes	Yes
55					Den	05/03/2013	12/03/2013		Yes	Yes	Yes
56					Donny	06/03/2013	13/03/2013		No	No	No
57					Jim	07/03/2013	14/03/2013		Yes	Yes	Yes
58					Jon	08/03/2013	15/03/2013		Yes	Yes	Yes
59					Jed	09/03/2013	16/03/2013		Yes	Yes	Yes
60					Ted	10/03/2013	17/03/2013		Yes	Yes	Yes
61					Tommy	11/03/2013	18/03/2013		No	No	No
62					Tim	12/03/2013	19/03/2013		Yes	Yes	Yes
63
Sheet2

You can use any of these formulas to achieve what you want.
The formula in I2 needs to be copied down.
The formula in J2 needs to be copied down.
The formula in K2 needs to be entered pressing ctrl shift enter NOT just enter, you can then copy it down.

I hope that helps.

Ak

 

[JoeMo]

Sorry Joe, not sure how to do this . . . If I enter the formula as you have done with {} the results return as all "No"

Enter like this in row 51, say cell H51 for example:
1. Copy the formula below, then select cell H51 (or any cell you want it in) and paste to the cell, but don't press enter.
2. With your cursor in the formula bar, hold down the shift and Ctrl keys and press the Enter key. You should now see the curly brackets surrounding the formula. Now you can copy the formula to other cells to cover your data range.

=IF(ISNA(MATCH(E51&F51&G51,$A$2:$A$100&$B$2:$B$100&$C$2:$C$100,0)),"No","Yes")

 

[nancybrown]

Thank Joe and Ak, I got this to work and learned something new. Thanks again.