INDEX and MATCH with multiple criteria

[wildturkey]

Struggling to get the foloowing work in Column L..Basically if I can match the value in column K in column C and any of those instances have "Sur" in Colum H then Id like to flag this in Colum L....

Surcharge2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Account	Date	Document Number	Customer	Status	Total Revenue	Item: Name	Surcharge			Document Number			SUR-1kg	Sur
2	Sales Orders	27/09/2021	SO302986	44684830 B	Billed	6.67	DEL1STDFSD				SO302986	0		SUR-3kg	Sur
3	Sales Orders	27/09/2021	SO302986	44684830 B	Billed	55.2	FS1233				SO302987	0		SUR-6kg	Sur
4	Sales Orders	27/09/2021	SO302986	44684830 B	Billed	12.37	S-GB				SO302988	0		SUR-9kg	Sur
5	Sales Orders	27/09/2021	SO302987	44684831 M	Billed	6.67	SUR-3kg	Sur			SO302989	0
6	Sales Orders	27/09/2021	SO302987	44684831 M	Billed	222.6	FS1234				0	0
7	Sales Orders	27/09/2021	SO302987	44684831 M	Billed	45.85	S-GB					0
8	Sales Orders	27/09/2021	SO302988	44684832 J	Billed	6.67	DEL1STDFSD
9	Sales Orders	27/09/2021	SO302988	44684832 J	Billed	166.32	IPBF05
10	Sales Orders	27/09/2021	SO302988	44684832 J	Billed	34.59	S-GB
11	Sales Orders	27/09/2021	SO302989	44684832 J	Billed	4.15	DEL1ECO
12	Sales Orders	27/09/2021	SO302989	44684832 J	Billed	12.5	SUR-3kg	Sur
report369

Cell Formulas
Range	Formula
K1:K6	K1	=UNIQUE(C:C)
L2:L7	L2	=INDEX(H:H,MATCH(1,("sur"=H:H)*(K2=C:C)),0)
F2,F5,F8	F2	=6.67
F3	F3	=55.2
F4	F4	=12.37
F6	F6	=222.6
F7	F7	=45.85
F9	F9	=166.32
F10	F10	=34.59
F11	F11	=4.15
F12	F12	=12.5
H2:H12	H2	=IFERROR(INDEX($O$1:$O$4,MATCH(G2,$N$1:$N$4,0)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.
Dynamic array formulas.

 

[Peter_SSs]

What actual results would you want in L2:L6 for that sample data?

Is column H simply marking rows that start with "SUR" in column G, or are their some "SUR ... " values in column G that do not get marked because they don't match with the values in column N?

 

[wildturkey]

Would like "sur" to be shown in L2:L6 please if both the SO number is shown in C and "sur" is shown in H please. There will be no SUR... in Column G that are not in N

 

[Peter_SSs]

In that case, unless you need it for something else, you do not need column H as far as I can see (or possibly columns N:O)

I would also recommend against using whole column ranges. In some cases that will force Excel to evaluate over 1 millions cells in each row, putting a significant burden on the calculation process.

See if this is what you want.

wildturkey.xlsm
	C	D	E	F	G	H	I	J	K	L
1	Document Number	Customer	Status	Total Revenue	Item: Name				Document Number
2	SO302986	44684830 B	Billed	6.67	DEL1STDFSD				SO302986
3	SO302986	44684830 B	Billed	55.2	FS1233				SO302987	sur
4	SO302986	44684830 B	Billed	12.37	S-GB				SO302988
5	SO302987	44684831 M	Billed	6.67	SUR-3kg				SO302989	sur
6	SO302987	44684831 M	Billed	222.6	FS1234
7	SO302987	44684831 M	Billed	45.85	S-GB
8	SO302988	44684832 J	Billed	6.67	DEL1STDFSD
9	SO302988	44684832 J	Billed	166.32	IPBF05
10	SO302988	44684832 J	Billed	34.59	S-GB
11	SO302989	44684832 J	Billed	4.15	DEL1ECO
12	SO302989	44684832 J	Billed	12.5	SUR-3kg
13
Sheet1

Cell Formulas
Range	Formula
K1:K5	K1	=UNIQUE(FILTER(C1:C1000,C1:C1000<>""))
L1:L5	L1	=IF(COUNTIFS(C$2:C$1000,K1#,G2:G1000,"SUR*"),"sur","")
Dynamic array formulas.

 

[wildturkey]

Thats what I asked for and muchj more - many thanks!!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.