Returning names based on 0 values...

[joe_h_95]

Any help would be greatly appreciated! I am looking to return Suppliers based on 0 values for certain people.

In Columns H-K, I want to return the top 3 Suppliers (and the value) based on the % in Column N, if their value is £0 within Columns B-E.

So with John for example, it would return Supplier 2 first and Supplier 6 second with no third place. Using Paul as an example, it would return by order Supplier 2, Supplier 5, Supplier 1.

I realise I may need another Column in between each person in H-K to return the % value too.

 

[Peter_SSs]

Any chance that you could manually insert the results you want for that sample data and post it again with XL2BB so that we can copy to test with?

 

[joe_h_95]

Here's the mini sheet...

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1		Paul	John	Neil	Dave			Paul		John		Neil		Dave
2	Supplier 1	$ -	$ 417	$ 14,443	$ -		1	Supplier 2	55%	Supplier 2	55%	Supplier 6	7%	Supplier 2	55%		Supplier 2	55%
3	Supplier 2	$ -	$ -	$ 57	$ -		2	Supplier 5	44%	Supplier 6	7%			Supplier 5	44%		Supplier 5	44%
4	Supplier 3	$ 14	$ 16	$ 18	$ -		3	Supplier 1	23%					Supplier 1	23%		Supplier 1	23%
5	Supplier 4	$ -	$ 718	$ 522	$ -												Supplier 7	20%
6	Supplier 5	$ -	$ 503	$ 259	$ -												Supplier 3	11%
7	Supplier 6	$ -	$ -	$ -	$ -												Supplier 6	7%
8	Supplier 7	$ 1,689	$ 3,438	$ 58,314	$ 1,687												Supplier 4	-1%
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B4:E4,G4	Cell Value	<0	text	NO

Thank you.

 

[Peter_SSs]

See if this helps.

22 08 04.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1		Paul	John	Neil	Dave			Paul		John		Neil		Dave
2	Supplier 1	0	417.0323877	14442.89348	0		1	Supplier 2	55%	Supplier 2	55%	Supplier 6	7%	Supplier 2	55%		Supplier 2	55%
3	Supplier 2	0	0	56.78839966	0		2	Supplier 5	44%	Supplier 6	7%			Supplier 5	44%		Supplier 5	44%
4	Supplier 3	14.38	16.04	17.62	0		3	Supplier 1	23%					Supplier 1	23%		Supplier 1	23%
5	Supplier 4	0	718.3632	522.2022	0												Supplier 7	20%
6	Supplier 5	0	503.1756	258.6496	0												Supplier 3	11%
7	Supplier 6	0	0	0	0												Supplier 6	7%
8	Supplier 7	1688.85255	3438.35555	58313.92665	1686.7767												Supplier 4	-1%
9
Suppliers

Cell Formulas
Range	Formula
H2:I4	H2	=IFERROR(INDEX(FILTER($Q$2:$R$8,XLOOKUP($Q$2:$Q$8,$A$2:$A$8,B$2:B$8)=0),{1;2;3},{1,2}),"")
J2:K4	J2	=IFERROR(INDEX(FILTER($Q$2:$R$8,XLOOKUP($Q$2:$Q$8,$A$2:$A$8,C$2:C$8)=0),{1;2;3},{1,2}),"")
L2:M4	L2	=IFERROR(INDEX(FILTER($Q$2:$R$8,XLOOKUP($Q$2:$Q$8,$A$2:$A$8,D$2:D$8)=0),{1;2;3},{1,2}),"")
N2:O4	N2	=IFERROR(INDEX(FILTER($Q$2:$R$8,XLOOKUP($Q$2:$Q$8,$A$2:$A$8,E$2:E$8)=0),{1;2;3},{1,2}),"")
Dynamic array formulas.

 

[joe_h_95]

Peter, this has worked so many thanks for your help - much appreciated!

 

[Peter_SSs]

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