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

 

[gordsky]

your request is not clear. Do all three conditions need to be met or is it an "or" equation. If all 3 need to be met why would E3 = matched when H3 doesnt equal H$. Please provide clearly what you require the equation to do

 

[RAJESH1960]

your request is not clear. Do all three conditions need to be met or is it an "or" equation. If all 3 need to be met why would E3 = matched when H3 doesnt equal H$. Please provide clearly what you require the equation to do

All the 3 conditions must match. I am matching Portal with Tally. So if the 2 other conditions are met then the first condition one should be Portal and the other should be Tally

 

[gordsky]

=IF(and(B2<>B3,C2=C3,H2=H3),"Matched","")

 

[RAJESH1960]

I am not getting the matches in both the rows. Please try and edit this formula which was shared for some other match. I am not able to edit it. The range is different in this formula

=IFERROR(IF(B2="PORTAL",F2,INDEX(F$2:F$20000,AGGREGATE(15,6,(ROW(F$2:F$20000)-ROW(F$2)+1)/((B$2:B$20000="PORTAL")*(C$2:C$20000=C2)*(H$2:H$20000>=H2-1)*(H$2:H$20000<=H2+1)),MAX(1,COUNTIFS(B$2:B2,"TALLY",C$2:C2,C2,H$2:H2,">="&H2-1,H$2:H2,"<="&H2+1))))),F2)

 

[gordsky]

which question do you want answering. The formula above doesn't resemble your initial request which I have provided an answer to

 

[RAJESH1960]

which question do you want answering. The formula above doesn't resemble your initial request which I have provided an answer to

I want the answer to my first query only. The formula I shared is just to make you understand better. It was a solution from a different query

 

[gordsky]

I want the answer to my first query only. The formula I shared is just to make you understand better. It was a solution from a different query

the answer to your first is at post #4. that will place "matched" into column E if the criteria you asked for is met

 

[RAJESH1960]

No. See my expected result and your result of the formula.

Query to match Portal+Tally.xlsx
	A	B	C	D	E	F	G	H	I
1	a	b	c	d	Gordsky's formula	f	g	h	Expected Result in column E
2	1445	PORTAL	03AAFCC9473R1ZZ		Matched			1924.36	Matched
3	1877	TALLY	03AAFCC9473R1ZZ					1924.36	Matched
4	571	PORTAL	03AAFCC9473R1ZZ		Matched			8555.43	Matched
5	1860	TALLY	03AAFCC9473R1ZZ					8555.43	Matched
6	1446	PORTAL	03AAFCC9473R1ZZ					9313.92
7	2118	TALLY	06AAACL2096A1ZZ		Matched			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	Matched
14	2496	TALLY	06AALFP8162H1ZX					4720.00	Matched
15	769	PORTAL	06AALFP8162H1ZX		Matched			18762.00	Matched
16	2495	TALLY	06AALFP8162H1ZX					18762.00	Matched
17	1557	PORTAL	06AALFP8162H1ZX		Matched			19706.00	Matched
18	2498	TALLY	06AALFP8162H1ZX					19706.00	Matched
19	1558	PORTAL	06AALFP8162H1ZX					23600.00
20	1559	PORTAL	06AALFP8162H1ZX					38940.00
21	770	PORTAL	06AALFP8162H1ZX		Matched			39613.00	Matched
22	2497	TALLY	06AALFP8162H1ZX					39613.00	Matched
23	636	PORTAL	27AACFE3300R1ZE					46800.00	Matched
24	638	PORTAL	27AACFE3300R1ZE		Matched			46800.00	Matched
25	1934	TALLY	27AACFE3300R1ZE					46800.00	Matched
26	1936	TALLY	27AACFE3300R1ZE					46800.00	Matched
27	1159	PORTAL	27AACFE3300R1ZE					53200.00
28	1551	PORTAL	29AAACB9461B1ZS					138516.00
29	222	PORTAL	29AAACB9461B1ZS		Matched			142800.00	Matched
30	1761	TALLY	29AAACB9461B1ZS					142800.00	Matched
31	2063	TALLY	29AAACI5569D1ZG		Matched			7943.76	Matched
32	1236	PORTAL	29AAACI5569D1ZG					7943.76	Matched
Match Portal & Tally

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

 

[RAJESH1960]

After matching if I sort the data to check the blank for mismatches, the rows which are matched with "Matched" will also be shown in the blank rows.