V-look up

[clemkonan]

Typically I would do a vertical look-up for a Supplier name by using a list and based on the ingredient name say Bananas in A1:A90 I can locate and return the Supplier name in C1:C90 however this case I need the capability to find multiple suppliers for that ingredient. One may be the "preferred ", the other may be the "alternate” and the last one may be “Emergency only". Can this be done without visual basic? If this solution is not a Vlookup I am still interested.
Thanks for help / feedback
Clemkonan

 

[Trevor_S]

How would you want the list of suppliers to be returned in a single cell?
How about filtering the data instead ... you can select the ingredient you want and see a list of suppliers.

 

[clemkonan]

I think I see your point. The destination or key deliverable is a risk assessment report, one specific for each of suppliers and Ingredients. I will be showing the risk associated with each Supplier and if approval status, and the risk classification for each ingredient. Two Suppliers can be providing a common ingredient and one may be high risk and the other low risk.

I think I will make a matrix of Supplier vs. Ingredient and color code to differentiate "Preferred” from” "Alternates". Only one supplier or Ingredient name can populate a given record (row) so I suspect filtering and using my matrix is the way to go.

Thanks for your feedback, huge help again.

 

[FDibbins]

See if you can adapt this to your needs...

	A​	B​	C​	D​	E​	F​
1​					aa
2​	name	Qty			name	Qty
3​	aa	10​			aa	10​
4​	bb	20​			aa	40​
5​	cc	30​
6​	aa	40​
7​	bb	50​
8​	cc	60​

E1 = Ingredient name
E3=IFERROR(INDEX(A:A,SMALL(IF($A$3:$A$8=$E$1,ROW($A$3:$A$8)),ROWS($A$1:A1))),"")
ARRAY entered, using CTRL SHIFT ENTER, not jet enter
then copy down and across as needed