Formula with multiple conditions

[RAJESH1960]

Hello guys,
I am trying to enter an if formula to get the result. But as there are multiple conditions I am not able to write the formula. I require your expertise to get the correct formula. The expected result is manually entered in column E. The 3 conditions to be included in the formula as shown in the image.

Query to match Portal+Tally.xlsx
	A	B	C	D	E	F	G	H	I
1	a	b	c	d	e	f	g	h
2	1445	PORTAL	03AAFCC9473R1ZZ		Matched			1924.36	=IF(B2<>B3,C2=C3,H2=H3,"Matched","")
3	1877	TALLY	03AAFCC9473R1ZZ		Matched			1924.36
4	571	PORTAL	03AAFCC9473R1ZZ		Matched			8555.43
5	1860	TALLY	03AAFCC9473R1ZZ		Matched			8555.43
6	1446	PORTAL	03AAFCC9473R1ZZ					9313.92
7	2118	TALLY	06AAACL2096A1ZZ					4602.00
8	397	PORTAL	06AAACL2096A1ZZ					4602.00
9	272	PORTAL	06AACCE2411Q1ZO					246400.00
10	1604	PORTAL	06AAFCC9473R1ZT					6879.82
11	1605	PORTAL	06AAFCC9473R1ZT					10200.96
12	379	PORTAL	06AAFCC9473R1ZT					11011.10
13	771	PORTAL	06AALFP8162H1ZX		Matched			4720.00
14	2496	TALLY	06AALFP8162H1ZX		Matched			4720.00
15	769	PORTAL	06AALFP8162H1ZX		Matched			18762.00
16	2495	TALLY	06AALFP8162H1ZX		Matched			18762.00
17	1557	PORTAL	06AALFP8162H1ZX		Matched			19706.00
18	2498	TALLY	06AALFP8162H1ZX		Matched			19706.00
19	1558	PORTAL	06AALFP8162H1ZX					23600.00
20	1559	PORTAL	06AALFP8162H1ZX					38940.00
21	770	PORTAL	06AALFP8162H1ZX		Matched			39613.00
22	2497	TALLY	06AALFP8162H1ZX		Matched			39613.00
23	636	PORTAL	27AACFE3300R1ZE		Matched			46800.00
24	638	PORTAL	27AACFE3300R1ZE		Matched			46800.00
25	1934	TALLY	27AACFE3300R1ZE		Matched			46800.00
26	1936	TALLY	27AACFE3300R1ZE		Matched			46800.00
27	1159	PORTAL	27AACFE3300R1ZE					53200.00
28	1551	PORTAL	29AAACB9461B1ZS					138516.00
29	222	PORTAL	29AAACB9461B1ZS		Matched			142800.00
30	1761	TALLY	29AAACB9461B1ZS		Matched			142800.00
31	2063	TALLY	29AAACI5569D1ZG		Matched			7943.76
32	1236	PORTAL	29AAACI5569D1ZG		Matched			7943.76
Match Portal & Tally

 

[RAJESH1960]

I understand an Index function in the formula will help, but I am not so good at it.

 

[gordsky]

I understand an Index function in the formula will help, but I am not so good at it.

can you explain why rows 7 & 8 do not count as a match in your expected matches example as they appear to fit the three criteria you stipulated

 

[RAJESH1960]

Row 8, the amount column, counts. Row 7 is blank.

 

[gordsky]

Row 8, the amount column, counts. Row 7 is blank.

Sorry do not understand your reply.
Your initial criteria were B7<> B8 so criteria met, C7=C8, criteria met, H7=H8, criteria met so by your inital request this should = matched

 

[RAJESH1960]

No problem. I was waiting for the solution. Time to hit the sack. Thanks for your time gordsky. Will try again tomorrow.

 

[johnnyL]

Where do I start?

Row 8, the amount column, counts. Row 7 is blank.

You are confusing the terminology. Rows go across the page, Columns are vertical (Up/Down the page). So when you say Row 7 is blank, you should have said Column 7 is blank. Think of it like a column that holds up the floor of a building, they are vertical.

No problem. I was waiting for the solution. Time to hit the sack. Thanks for your time gordsky. Will try again tomorrow.

The formula @gordsky posted in post #4 meets the criteria that you mentioned the formula should do.

I think I can see what you are trying to do, but just when I thought that, your expected results don't meet what I think you are trying to do.

Let me explain what I thought you were trying to do based on your 'expected results'. It appears that you are trying to 'pair' up matches that meet your criteria and when the 'match' to your criteria is met, you want both of the 'matches' marked as a 'matched' and then move on to the next row and look for another 2 consecutive 'matches' to your criteria. The problem I ran into with that thinking is that some of the results you marked as 'matched' in your 'expected results' do not meet the first criteria for the B column.

Perhaps rethink what you are actually wanting to do and make another post here with any clarifications that you think should be made.

 

[RAJESH1960]

you are trying to 'pair' up matches that meet your criteria and when the 'match' to your criteria is met, you want both of the 'matches' marked as a 'matched' and then move on to the next row and look for another 2 consecutive 'matches' to your criteria

Exactly. I just finished matching 20,000 rows yesterday. It took me 4 hours to match manually. I thought a formula will make it in seconds. I will try to explain again. The original data looks like in the below image. I sorted the data by column B as that is the only common factor between PORTAL and TALLY. Then I filter the headings and deleselect all and check each ID No. at a time and check the amounts. If the amounts are the same and ID No. is same, it is a match. I manuallly enter Matched in each of the rows.
If I apply a formula in column E, I don't have to sort nor filter. The formula will take the ID No. & the amount in Portal and search for the same in TALLY rows and display matched in both the rows.

Query to match Portal+Tally.xlsx
	A	B	C	D	E	F	G	H
1	a	b	c	d	Gordsky's formula	f	g	h
2	222	PORTAL	29AAACB9461B1ZS					142800.00
3	272	PORTAL	06AACCE2411Q1ZO					246400.00
4	379	PORTAL	06AAFCC9473R1ZT					11011.10
5	397	PORTAL	06AAACL2096A1ZZ					4602.00
6	571	PORTAL	03AAFCC9473R1ZZ					8555.43
7	636	PORTAL	27AACFE3300R1ZE					46800.00
8	638	PORTAL	27AACFE3300R1ZE					46800.00
9	769	PORTAL	06AALFP8162H1ZX					18762.00
10	770	PORTAL	06AALFP8162H1ZX					39613.00
11	771	PORTAL	06AALFP8162H1ZX					4720.00
12	1159	PORTAL	27AACFE3300R1ZE					53200.00
13	1236	PORTAL	29AAACI5569D1ZG					7943.76
14	1445	PORTAL	03AAFCC9473R1ZZ					1924.36
15	1446	PORTAL	03AAFCC9473R1ZZ					9313.92
16	1551	PORTAL	29AAACB9461B1ZS					138516.00
17	1557	PORTAL	06AALFP8162H1ZX					19706.00
18	1558	PORTAL	06AALFP8162H1ZX					23600.00
19	1559	PORTAL	06AALFP8162H1ZX					38940.00
20	1604	PORTAL	06AAFCC9473R1ZT					6879.82
21	1605	PORTAL	06AAFCC9473R1ZT					10200.96
22	1761	TALLY	29AAACB9461B1ZS					142800.00
23	1860	TALLY	03AAFCC9473R1ZZ					8555.43
24	1877	TALLY	03AAFCC9473R1ZZ					1924.36
25	1934	TALLY	27AACFE3300R1ZE					46800.00
26	1936	TALLY	27AACFE3300R1ZE					46800.00
27	2063	TALLY	29AAACI5569D1ZG					7943.76
28	2118	TALLY	06AAACL2096A1ZZ					4602.00
29	2495	TALLY	06AALFP8162H1ZX					18762.00
30	2496	TALLY	06AALFP8162H1ZX					4720.00
31	2497	TALLY	06AALFP8162H1ZX					39613.00
32	2498	TALLY	06AALFP8162H1ZX					19706.00
Match Portal & Tally

Cell Formulas
Range	Formula
E2:E32	E2	=IF(AND(B2<>B3,C2=C3,H2=H3),"Matched","")

 

[RAJESH1960]

Columns 4, 5, 6 and 7 are also filled with some data which will not help to match. So I kept them blank in the image.

 

[gordsky]

Columns 4, 5, 6 and 7 are also filled with some data which will not help to match. So I kept them blank in the image.

you really need to get the terminology correct when you make requests, columns are letters, rows are numbers..................
you said column 5 already has data in it (i presume you mean column E) but say you want to place a formula in it...???
The below formula should do what you are asking but will show ROWS(the ones that go horizontally) 7 & 8 as a match as they meet ALL of your criteria (totals and ids match). as per post#14 why aren't they shown as a match in post#11 (original explanation).

=IF(MATCH(1,(H2=$H$2:$H$32)*(C2=$C$2:$C$32)*(B2<>$B$2:$B$32),0),"matched","")

 

[RAJESH1960]

you really need to get the terminology correct when you make requests, columns are letters, rows are numbers..................
you said column 5 already has data in it (i presume you mean column E) but say you want to place a formula in it...???
The below formula should do what you are asking but will show ROWS(the ones that go horizontally) 7 & 8 as a match as they meet ALL of your criteria (totals and ids match). as per post#14 why aren't they shown as a match in post#11 (original explanation).

=IF(MATCH(1,(H2=$H$2:$H$32)*(C2=$C$2:$C$32)*(B2<>$B$2:$B$32),0),"matched","")

Sorry. Column E which is the 5th column is inserted to match, is blank where the formula has to be posted.